The string is non-numeric

[roboticapps1]

I haven't programmed in Crystal Reports much so I don't know all the syntax but from what I've read thus far, this is what I have. Unfortunately, I get an error message that says

The string is non-numeric.
Details: errorKind.

It doesn't give me much information about where the problem is so I'm a little stuck. Here is the formula that I have. An explanation of the formula or what I was looking to do is at the bottom.


//Formula 1
shared numbervar machRAN;
shared numbervar machNUM;
shared stringvar machLET;
local stringvar machDOWN := "DOWN" + chr(13);
local stringvar output := "";

If {BAQReportResult.LaborDtl.ResourceID}[1] = machLET AND toNumber({BAWReportResult.LaborDtl.ResourceID}[2] = machNUM Then
(
{BAQReportResult.LaborDtl.ResourceID} + {BAQReportResult.EmpBasic.Name};
)
Else
(
While {BAQReportResult.LaborDtl.ResourceID}[1] <> machLET AND toNumber({BAWReportResult.LaborDtl.ResourceID}[2] <> machNUM
(
machNUM := machNUM + 1;

If machLET = "A" AND machNUM > 6 Then
(
machNUM = 1;
machLET = "B";
)
Else If machLET = "B" AND machNUM > 6 Then
(
machNUM = 1;
machLET = "C";
)
Else If machLET = "C" AND machNUM > 2 Then
(
machNUM = 1;
machLET = "D";
)
Else If machLET = "D" AND machNUM > 4 Then
(
machNUM = 1;
machLET = "E";
)
Else If machLET = "E" AND machNUM > 2 Then
(
machNUM = 1;
machLET = "F";
)
Else If machLET = "F" AND machNUM > 4 Then
(
machNUM = 1;
machLET = "G";
)
Else If machLET = "G" AND machNUM > 8 Then
(
machNUM = 1;
machLET = "H";
)
Else If machLET = "H" AND machNUM > 1 Then
(
machNUM = 1;
machLET = "J";
);

If {BAQReportResult.LaborDtl.ResourceID}[1] = machLET AND toNumber({BAWReportResult.LaborDtl.ResourceID}[2] = machNUM Then
(
output := output + {BAQReportResult.LaborDtl.ResourceID} + {BAQReportResult.EmpBasic.Name};
)
Else
(
output := output + {BAQReportResult.LaborDtl.ResourceID} + machDOWN;
);

);
output;

);


Our company has rows A - H of machines. A1-6, B1-6, C1-5, D1-4, E1-2, F1-4, G1-8, H1. I'm trying to output a list of all the machines and the work that has been done on each machine during the day. This would be completely easy if all machines were running but there are cases that a certain will be down. I need to document which machine is down on the report and that is where the formula comes in. The data that I'm using might have all machines except B6 and F2 in which case those machines are not going to be included in the data so I need to "manually" insert them in the crystal report to show that those machines were down.

Looking at the formula, can someone tell me why I'm getting that error?

Thank You.

 

[MCuthill]

roboticapps1,

In scanning briefly over your formula, I believe you must have non-numeric (alpha) data in one of the fields you are casting with [blue]ToNumber[/blue]() function in your formula. Sometimes Crystal tells you the record in error in the Formula Editor when the report is ran (in CR10) - you could use this to confirm.

In short, I believe Crystal is telling you the data found cannot be converted to a number as no number exists in the field for that record.

Hope this helps. Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[roboticapps1]

Thanks MCuthill for your response. The toNumber() function is working correctly, I have verified that.

 

[MCuthill]

hmmm... Drat.

I am afraid that was all I could come up with from reviewing the formula roboticapps1, hopefully another Tek-Tipper can help you out!

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[briangriffin]

I don't think this is the entire problem, but you need to close the parens in your tonumber functions -

toNumber({BAWReportResult.LaborDtl.ResourceID}[2]

needs to be

toNumber({BAWReportResult.LaborDtl.ResourceID}[2])

That would normally result in a ") is missing" error, but once corrected it might give you insight into what is really happening.

 

[roboticapps1]

Brian, thanx for the response. I know this is going to sound weird but I had to retype the entire formula from one computer to another. My systems/network computer and my internet computer are completely separate and I can't stick in a usb drive into the systems/network computer (company policy). Long story short, I missed that when I was retyping it on the internet computer but thank you.

I've been reading about the crystal syntax and I found some articles describing that the IF statements must have the same data type output in the IF and the Else. Is this true if there is a IF statement nested inside of a IF statement.

I'm not sure what I changes within the last few hours, which seems like I haven't changed anything but now I'm receiving another error that says,

A string can be at most 65534 characters long.
Details: errorKind

I assume that the formula works at least a little now but I'm not sure why a string would get so long. I've made output and machDOWN local stringvars which I assumed would reset to the the value that I assigned every time it runs.

Any thoughts on that?

 

[briangriffin]

In which report section does this formula reside? How many records are you expecting to return? What groups exist?

I'm guessing that you would need to reset the output variable at some point:

output := output + {BAQReportResult.LaborDtl.ResourceID} + machDOWN;

If you keep adding to the output variable then at some point it would exceed the 65534 limit, again this depends upon the placement of the formula and the records returned.

I would try changing the formula to:

output := {BAQReportResult.LaborDtl.ResourceID} + machDOWN;

Just to try to get past the error and see where that leaves you.

Also, if I'm reading this right you're trying to insert machines that are down; in other words machines where no records exist. This would be similar to the age old problem of having a record for every date in a month, even if there were no records for some of the days. Is this the problem you're trying to solve?

 

[roboticapps1]

More or less, yes. Is there a more simple solution for this type of problem?

 

[briangriffin]

Don't get me wrong, I like your aggressive approach!

I would create a table in the database (if possible) that simply holds one record for each machine. The do a left outer join to the table that holds the daily data.

If you can't create tables, you could create a command object within the report - something like

select "A1"
union
select "A2"
union
select "A3"
etc. etc.

Then do a left outer join from the command object to your data table.

 

[roboticapps1]

Thanks for the help but I must not be knowledgeable enough to follow that. I won't be able to create new tables so that can't be an option and I might not know the terminologies but I'm not sure what the command object within the report would be. If I'm following your train of thought, it seems like a great way to go and as long as a specific machine is empty (or has no records), I can just mark is as DOWN or inactive etc. However, I'm not exactly sure how to do that. Would I create X number of Formula fields and each field would have Select X machine?

 

[briangriffin]

In the Database Expert, under the connection you've defined, above Tables, you'll see "Add Command". Double click it to open a new blank window where you can type any valid SQL statement. Since you don't need to pull any actual data for this to work, you don't have to worry about database-specific syntax. Type this in exactly:

select 'A1' as Machine
union
select 'A2'
union
select 'A3'

etc. until you've listed all your machines. Click OK and go to the Links tab in the Database Expert. You'll see the command object, which now behaves exactly as a table would.

I think it makes visual sense to put the command on the left side of the window. Drag from the Machine field in the command to the corresponding field in your data table to create the join.

Once the join is created, double click on the blue join line itself. That brings up the Link Options dialog. Click the Left Outer Join button. That tells Crystal to include all records from the left table, and only matching records from the right table.

Now when including the machine name field in the report, make sure you use the one from the command object (table), not the one from your data table. That should give you every machine, regardless of whether it was down that day.

I'm heading home, so if you get stuck someone else can probably help you with it today, or I'll take a look tomorrow.

 

[roboticapps1]

Brian,

I was researching commands and the method that you had purposed, however, I'm not sure if I will be able to do that because of the way the way my data is connected. I'm actually using an ERP system that creates an XML file and that crystal uses to format. So in my Database Expert, my connection is currently ADO.NET (XML) which, I believe, does not have the option for "Add Command".

I should have explained that in the beginning, my apologies.

 

[briangriffin]

Back to your original formula, then - try modifying the output variable assignment as described above to get past the size limitation error and see where that gets you.

Also please post the groupings in your report and the location of this formula within the report.