Sage 100

Freight that is printing on invoices is off by one cent(.01) **Sage 100 advanced version 2014 SP 3.** Has anyone ever seen crystal change the value in a field by one cent? I know you will think I'm nuts to ask that question, but I've looked at this and 2 other consultants here have as well and we all agree we may be experiencing group hallucination or something. I Realize one cent seems like not much to worry about but that freight total is added to the invoice amount on the printout and these invoices go to the customers who are then paying the wrong amount. **Short version/ example**:DFDM shows freight as $76.57 we put that field on the invoice (no formula or calculation we JUST put the field on the invoice form!) and it shows $76.56 (see the attached screen shot) **Here is the longer version of the story with several different things we have tried** Client called us a couple of weeks ago to tell us that the freight that is printing on invoices is SOMETIMES off by a penny. It is random and they don't know any consistent reason for this happening. they showed us screen shots of invoice history where the Freight is correct and PDF copies of the invoice from paperless office where the freight was off by the penny. We told them that we needed an example that was still in invoice data entry so we could figure out why it was happening. That is when the fun started. We expected to see some extended decimal in the freight field or something causing a rounding of some sort. But using DFDM to look at the SO_InvoiceHeader we could see that the freight showed correctly and only 2 decimal places So we looked at crystal and used a SAGE STANDARD form and the freight is WRONG (off by a penny). If you look at the standard form the freight field is simply the field. (no formula/calculation anything). We tried manually changing the value of the freight in SO_InvoiceHeader table using DFDM and the results did not change (still prints wrong amount of freight) So now we preview the report and look at the worktable expecting to see that somehow Sage populated the wrong value in the worktable. NOPE! the value in the worktable is correct! But the value printed on the invoice form is incorrect. We added a UDF to the worktable and used data sourcing to populate the UDF with the freight amount in the worktable and we see exactly the same results. (Worktable shows correct value but putting that field on the invoice form prints an incorrect amount) We made a generic crystal report and pointed it at the work table and we also see the WRONG value for these fields on the generic crystal report. We used a SQL query to pull the field into SQL and it is WRONG in SQL (in other words, the amount in SQL is the same as what is printing on the invoice).We pulled the work table onto a local machine and saw the same results in SQL. Is it possible that the value we see in DFDM is not what is truly in the table? That seems unlikely since we tried manually changing the value using DFDM and it did not change the results. Basically at this point we cannot get crystal to print the correct value on the invoices. I want to think that it is not a SAGE issue but a Crystal or and ODBC issue of some sort, but the fact that it keeps happening to ONLY one or two invoices in a batch of over 70 invoices makes me think it can't be crystal but a data issue of some sort. It does not seem to be rounding since the crystal is printing a smaller number that what we see in DFDM.

No, I just can't deal with something like that today. Please just tell me that you decided it was group hallucination and you found the answer and it was something really silly.

I wish I could! or I wish I could tell the client to deal with the penny differences; But sadly I have to say I've spent WAY more pennies than this problem seems to deserve and now I'm almost obsessed with finding the cause; but I would settle on finding a way to print the invoice correctly even if I can't figure out a cause.

Have you tried to reinitialize the work table for invoice printing?

@DavidOverholt, the only explanation I can come up with is that the freight field in the Sage 100 table is actually being stored with more than 2 decimal places but DFDM is only showing two decimal places. That's not how it is supposed to work, of course. However, I believe it could happen under some circumstances. The way that I would do it is get a program to start a console session (SYZCON). Type: PRECISION 6 <ENTER> to make sure you are allowing enough decimal places. From there use something like: F=%sys_ss'OpenTable(""SO_InvoiceHeader"",""COMPANY"") <ENTER> to open the file. Then use a READ(F, KEY=""xxxxxxx"") <ENTER> to get the invoice number. (obviously you would replace xxxxxxx with your invoice number. Once that's done you can: ?FreightAmt <ENTER> and see what it says. That will be sure to show all the decimal places, if there are any more.

I meant to say ""Get a programmer to start a console session"". However, you could certainly do it yourself with those crystal clear instructions I wrote out, yes? :-) Good luck!

@DavidOverholt... in my earlier post I had indicated where to hit the enter key by using less than ENTER greater than, but they do not appear in the comments after I posted it. Here are the command line entries that I meant to tell you to try: PRECISION 6 F=%sys_ss'OpenTable(""SO_InvoiceHeader"",""COMPANY"") READ(F,KEY=""xxxxxxx"") PRINT FreightAmt

Well good idea - but results are not what I had expected. see attached screen shot in case I did it incorrectly. I just tried reinitializing the WRK table (Manual re-initialization) that is a good idea too, but it did not work.

are you dfdm'ing (not really a verb) the invoice header file or the invoice header work file. you may wish to preview the form, the do DFDM against the wrk file, by looking in SOXXX folder by date ..... hope I'm clear - if it is getting into the workfile wrong, this would help isolate where is issue really is. good luck

We are looking at the work table and the value in the worktable is different than what is printing on the invoice. That is exactly what is so frustrating about this I like your verb though!!!

I should mention that some invoices are updated through ScanForce. we are talking to them also however since it looks right and data file display and maintenance I am not sure that it is related.

What happens if you reprint the invoice from AR History? What about grabbing their data set and trying it in v2016? Change the decimal precision on the Crystal form to see what prints out? How about modifying the Crystal form to calculate with a formula rounding? Yes, none of these suggestions address the root issue, but may shed some light.

OK I can still learn something new after almost 20 years of doing this. apparently Data File display and Maintenance or something is rounding for us. I made a copy of the SO_InvoiceHeader table and looked at the copy file using DFDM and as you can see in my attached screen shot, when you do this Sage doesn't recognize the file name and does not format or round the freightAmt. If I had to guess, the data class of the **FreightAmt** variable is **currency** and everywhere in Sage including with the ProvideX commands it is formatting the field and not showing the actual value. the actual value in the field is 76.5699999999999872 Thanks everyone for the ideas and suggestions !!!! it is greatly appreciated as always. I think at this point I can conclude that these are coming from a ScanForce scanner. I'm still not sure how to make a variable like this print correctly but at least I can quit obsessing over it! Jeff, I did try your latest suggestions. printing from history produced the same (incorrect) results. Changing the precision and A Rounding formula produced the same too!. I did not copy there data to upgrade to 2016 (32GB for just AR/SO/IM modules YIKES)

Do they have freight schedules with calculated rates? How would it get to extended decimals without some sort of calculation?

that is a very good question! We are looking at that this morning

As an interim measure, you could write a quick program to read and write the SO_InvoiceHeader to round it to two decimal positions prior to printing....

We found this morning that ScanForce is **not** sending the freight into Sage, so we are at a loss to see the cause, unless Sage is trying to calculate the shipping (rate as @ThereseLogeais) mentioned. We looked at the shipping rate maintenance and everything in there is zero. I think we are going to turn off the flag to use shipping rate maintenance to calculate freight. But then if they have to add new SHIP VIA records they will have to turn it on temporarily. we did write a script that triggers any time the freight field changes to round as @JeffFiddelman suggested and write that back into the freight field.. We hope this is going to work, but at this point I'm not sure if the script will see all the decimal places and do the rounding properly or not. Our fingers are crossed and the client is going to manually look at each printed invoice and compare it to the value on the screen around noon to see if they can find any incorrect ones.

If you need a quick MD developer program to solve this issue in the interim, just email me directly at jfiddelman@exeplex.com

Thanks Jeff, if the script does not work, I'll let you know.

I thought I would update anyone that might be interested. We finally found the culprit to the freight rounding issue. It was in a script that we put on the client system. The script was triggered whenever the user changed the freight. we had looked at the script several times to see if it was the culprit and we did not think it was since the script is not doing any calculation to the freight amount. We still don't know WHY the script caused the rounding issue, we were simply looking at the value in the payment type and doing other calculation if the payment type was a credit card. however the last line of script was : myRetVal = oBusObj.SetValueNoValidate(""FreightAmt"",myFreightAmt) I'm not sure why ""NoValidate"" was used in the script (i did not write the script) but I changed the script to myRetVal = oBusObj.SetValue(""FreightAmt"",myFreightAmt) so the validation is done and that corrected the problem.