Sage 100

I know it's late on a Friday afternoon but I'm trying to update a customer's item pricing that they get from a vendor. The problem is, the vendor sells in cases and they sell in eaches. I tried using the purchase u/m conversion factor from CI_Item (as temp numeric field temp002). I also have the vendor price as temp001 and the calc is temp001/temp002 but it errors out. Thoughts??

Any zeros or blanks in the source for temp002?

Howdy, Therese! Use the UOM conversion maintenance. You will need to set up the vendor's case quantity (ex: CS24 is 24 of EACH) and then you only need to import the vendor's case price and purchase UOM, Sage will take care of the each price using the UOM conversion maintenance for you.

Good thought but they have MANY different case sizes. They are doing it in Item maintenance. And the vendor doesn't provide the case size in the file.

Yeah, I've dealt with that in the past. You kind of have to deal with that. I don't suppose they already have the case size set up in the item? Probably not, that would be too easy, because then you would only need to import the case price.

Actually, I am trying to import the standard cost from the vendor's price/cost, not the vendor pricing. (Sorry, as I said it is getting late on a Friday afternoon!)

So the std cost = case price/purch conversion factor - which they have in item maintenance e.g. std u/m=Each and the purch u/m =Case with a conv. factor of say, 6. So if the case price = 30.00, the std cost s/b 5 but VI won't cooperate.

I see. It isn't a factor from the vendor case price? Meaning, set the vendor price for the case, then use the calculated each price and generate the standard cost (typically each price * .60 or similar)?

what version of Sage?

2017 5.40.2

Then import your calculations into standard cost adjustment entry. That way you can see it and then apply when you think it is right.

Is it reporting a specific error number like 26 (variable type invalid) or 40 (divide by zero)?

Error 20 VIWxxx line 6xx (don't have it in front of me now). Bob, you can't import into std cost adj unless the valuation is std cost.

That could indicate a syntax error, did you check the calculation for errors? Did you go into the calculation builder screen or just type it in?

I went into it and checked and it said it was fine.

Do you have any conditional formatting? Are you sure you have all your temp fields defined correctly as string or number? When referencing any fields (table and temp), do you have the $ suffix for string and no suffix for numbers?

Sorry, it is Friday and just back from the bar. Can you key the standard cost in item maintenance? Will case value import without any calculation? Do you have a rounding (PRC) in your division formula prc(temp001/temp002,2)?

Well, no, I do not have any rounding functionality. Gee, may try that later.

@DavidSpeckII they're both numbers so no suffix for either. @JeffSchwenk the first purch. u/m factor is 1 so there shouldn't be any rounding issues. I'm stumped.

Like Jeff said, does the import work if you remove the calculation? Can you try testing with a source file with a single record and a value to replace the calculation? Are you using any select or record operations? If yes, are you positive they are all using the correct syntax?

Yes, I can import if I don't do any temp for the purch. u/m. I don't think it's the calc, I think it's the file assign of the conversion factor. The only select option is to skip the header which works fine if I only test one record, which it skips successfully. It's when I do the first item record that it blows. I checked the syntax and it seems fine to me. Here is the import job, the calculation, and the error.

That is a different error, could indicate it is referencing a property or method of an object handle and the object handle does not point to a valid object. It says the error is occurring in the VI program, can you attach it?

Can you attach the actual program file from the VI folder? The file name is the same program listed in the error.

You may want to try creating a new import definition from scratch. The file you attached says the job name is ""AR_Customers"" and is attempting to import into AR1XXX.soa. At any point in time did you have two VI import windows open at the same time while creating the ULF import? I was just recently creating two new imports and was working on them simultaneously, i hadn't compiled one prior to starting the second, when i compiled the first and then later the 2nd, the first ran fine, but i notice when i went to run the 2nd, it was executing the same job from the first. i think i ended up exporting and then importing and i think i renamed instead of overwriting.

I don't recall but I will start over - thanks for all your help @DavidSpeckII!

Hey @DavidSpeckII - that first VI file I sent was from their test upgrade. My bad. This one is from their live install. Hopefully, it doesn't say AR_Customer but points us in the right direction this time!

The M4T extension should indicate the that is the data file, not the program file. I can't verify that at the moment but if you can get the program file, I'll take a look at it shortly.

The above is a new job - I deleted the original and recreated it. I am still getting the Error 20.

In your screenshot of the fields, you have temp field 1 and 4, however in the VI program file you attached, i only found a reference to temp fields 1 and 2. They both appear to be defined as string fields too. There is also one line flagged as invalid, it is ""00970 var$ = CI_Item.PurchaseUMConvFctr"" which is definitely invalid.

See my post above. This is the new job.

Why is the PurchaseUMConvFctr invalid?? It is a numeric field.

I think you may have found a bug. I just created a new job on v2015 (no PUs) with just the item code and a temp field using file assign from the PurchaseUMConvFctr filed and after it compiled i checked it and the same line is being flagged as invalid.

Great....

Instead of using file assign, try setting it to calculated and then in the builder, reference the {CI_Item.PurchaseUMConvFctr} field. I just compiled it using this method and no errors in the compiled program.

Oh, and make sure the temp field is defined as numeric.

The other possible work around that i think would work is use file assign like you were but define the temp field as a string, then in another temp field use a condition to check if the first temp is a valid numeric value and if so, use a calculation to convert the first temp to a number.

You're a genius @DavidSpeckII! I used the actual field instead of a temp and it worked!!! Yay!!!