Sage 100

One of our customers would like to change the UOM for the majority of their inventory items. The items to be included currently have standard UOMs of dozens and half-dozens. The objective is to change all of those items to use a standard UOM of each. Both the UOM itself and the quantities would need to be changed in the open order tables, open invoice tables, and all of the historical tables that contain UOM. Changing the UOM in the open tables is pretty straightforward. I'm wondering if there is a feasible way to make the change in the historical tables and if anyone in the group would be interesting in taking on that part of the project.

This would be for Sage 100 Advanced version 4.45.

How much history is there?

Almost 4 years. We purged a chunk of history when they moved from 3.74 to 4.45.

Any idea on the # of records?

In which table?

AR Invoice History Detail.

If you change the history then not only is it there a change to UOM but also the quantities too, the extensions as well. Is it really necessary to change history?

I agree with Moira. The history would be in old UOM, but that was the measure at the time!

@MoiraGoggin, you're exactly right about the calculations. They use the history for reporting purposes. @GaryFeldman, I thought about that too. Could they just use formulas in their reports to compensate for the difference in UOM? @JeffSchwenk, confirming the number of records in the history table.

Approximately 3.2 million records in AR Invoice History Detail.

So the issue is that the history may show 1 when the UOM conversion would mean 6 or 12 each if you just pull a quantity field due to UOM. Then the question would be what reports are they using for what purposes? How far back do these reports look? Could Sage Inventory Advisor be a different option? The reporting in there is pretty good and you could do the conversion in the upload and then set scripts to only pull new.

Changing the formulas would be my suggestion. In fact, there is a UOM conversion factor in the table already so not that difficult to convert to EACH's. Unless, of course there was no conversion factor. Still, not too difficult to do the conversion in a formula.

@JohnShaver I second @ThereseLogeais idea - also would add that you could use the invoice date (if date prior to XX/XX/XXX) add an asterisk or other indicator to let the user know that this is the historical UOM.

Don't you guys realize this is not about meeting customer requirements, but selling more sage products, especially ones like SIA which give little or no compensation and extend the roots of the sage tree in the customer pockets? Sorry, @PeterWolf invaded my body for a moment.

?_? SLA has zero to do with Sage. Can't tell if you are joking or not ... I have repeatedly told them that they need to change the name though because it is confusing.

Ignore my comment ... I'm suffering from little to no sleep because of a bug I can't seem to shake.

SAGE INVENTORY ADVISOR NOT SAGE LEADERSHIP ACADEMY....

John, I think the tribe has spoken. I too vote for @ThereseLogeais idea of formula driven translations. And I will take it one step further and suggest an Access PTQ intermediary database to speed reporting along......

I'll recommend that they go down the formula path. I don't think they really want to make the changes to the history tables either. They jokingly asked if I would do it for a million dollars...my answer was ""no""! Jeff, since they are running Sage 100 SQL, report speed shouldn't be an issue (unless I'm missing something). Thanks everyone!

@JohnShaver Me thinks Jeff suggested PTQ because you stated this was 100 Advanced (but it's actually Premium).

Let's blame it on Sage and their dipshit rebranding!

A ""middle-ground"" approach could be to update the UnitOfMeasureConvFactor in AR_InvoiceHistoryDetail. It would make reporting a bit easier and it's not too invasive.