Error in Decimal subtraction

RenatoE
Participant III

Can a LOOKUP formula introduce errors in decimals?
The formula deduces from OnHand column the sold Quantity stored in Sales Table of each Item.
All the integer results are perfect, but decimals are not subtracting correctly. Usually the result of this subtraction is larger so I wonder if LOOKUP is rounding up?

([OnHand] - LOOKUP([_THISROW].[Item],SalesTable,Item,Quantity))

0 32 1,014
  • UX
32 REPLIES 32

Could you share the data types for

[OnHand]

[Quantity]

fields?

I assumed both fields are currently set to Number type so the result is always number, cut off the decimals.

Hi,
Both are decimal

@Steve

I need Steve to coach us for this kinda subject.
This kinda of Appsheet calculation will not bring the result which we learn at the school.

It is a strange behavior, the items that counted in integers have no problems and the subtraction results are always correct. The problem arrives when an item is entered in decimals.

Also I should note that the operation is initiated using the multiple item selection box on the top right side. I select 30 to 60 items at a time to do the subtraction line by line.
Thanks

Is it possible there is a mismatch in the Decimal digits setting of the two Decimal columns?

Thanks Steve, the related items are in two decimals. That is the inventory table quantity is with two decimals, and the table that contains the items to be subtracted has two decimals.

Do you have a sample with a result?

Hi,
I will prepare some same numbers with the results tonight.
Thanks you,

I have the example right out of the APP.

The beginning inventory table:


Bodega is where the OnHand quantity is shown.
The table that has the items to be deducted:

The APP screen when I select the items to be subtracted:

The ending inventory table:

If you do the math:
Beginning inventory โ€œMedidor Sagaโ€ is 4
Subtracting Qty of 3 from the orders
Final Inventory = 1
This shows that the integer items work properly.

Beginning inventory โ€œTubo 1/2โ€ฆโ€ is 19.3
Subtracting Qty of 3 from the orders = 0.2+0.3+0.4 = 0.9
Final Inventory should be = 19.3 - 0.9 = 18.4
But it shows final inventory of = 18.7
This shows that the decimal subtraction is wrong.

The other item: โ€œCemento Holcimโ€
Also gives wrong results.

Finally I record the changes item by item on confirmationtable shown below:


This shows that the items subtracted are correct and in the correct quantities.

THE ACTIONS:
The multiple action that records the transaction and then substracts the items:

The subtracting action:

Thanks for your patience and help!

@RenatoE

Actually, I just re-read this. Your intention is to SUM all of the SalesTable rows where Item = โ€œTubo 1/2โ€ฆโ€.

Do I have that right now?

If so, you canโ€™t use LOOKUP() for that. Youโ€™ll need to do a SUM(SELECT()) expression.

@RenatoE

The expression should be something like:

([OnHand] - SUM(SELECT(SalesTable[Quantity], [Item] = [_THISROW].[Item]))

I may consider doing this sum on the next iteration. I was unable to make that work so I implemented a line item by line item using the checkbox on the Sales Table as shown below:

@Steve what does LOOKUP() do when multiple rows match the lookup value? Does it just use the FIRST row for the return value?

It returns one found value at random, typically the first, but โ€œfirstโ€ isnโ€™t always what one might expect, and may change unexpectedly.

I will probably add a UNIQUEID() to the inventory table just to make sure that this is not an issue.
Thanks

Yes, I would recommend doing that.

Unfortunately, thatโ€™s not where you are encountering an issue. In your expression to adjust Inventory count, you need to be able to select the correct SalesTable row (or rows) to use in that calculation.

I hope that is making sense for you??

@RenatoE

First, let me just say that Inventory management is NOT trivial in a distributed multi-user platform like AppSheet. Certainly doable but requires a little extra care.

There are holes whichever way you go.

Line By Line Method
Line by line updating can work but not with LOOKUP() as written because the search by โ€œItemโ€ will return multiple rows, grab one of them but you donโ€™t know which one it might grab. The LOOKUP() function would need to KNOW which row to retrieve. This is usually done by the ID of that specific row. In your case, the LOOKUP() needs someway of knowing which row ID to use.

Even when you trigger an update based on the Sales record being added, once you have traversed the Actions to get to the point to modify Inventory, that action does not have knowledge of which row prompted the Inventory update. You can use a flag or marker to indicate which row and use that in the LOOKUP(). Then unmark it after the update. This can become problematic when several users are making changes at the same time. Though summing all marked rows may help counter that.

SUM Method
Using the SUM() method I presented will work BUT you need to mark the rows that were accounted for and exclude them when the next update occurs. Again, using a flag or marker but this time marking them AFTER the updates and the marker stays put.

Other
Other methods are possible depending on your app.

There are other considerations as well if you decide to support Quantity modifications/updates.

Inventory processing would be MUCH easier if we had an Action to โ€œUPDATE a row in another table using values from this rowโ€. I think Iโ€™ll get a Feature request added for this, if not already there.

There is a Feature Request on the list. Please go and Upvote it!!

The APP is not adding the sales table. It is processing item by item and also deducting from inventory item by item until the entire table is processed.

It is important to note that the system is working for all items in integer form. It is only failing to process the decimals correctly.

These yellow triangles need to be fixed. They indicate broken Ref values.
2X_4_44fd7541a1d782f365f7ea16ec0aca315b210881.png

Thanks, I will look into this broken Ref values. I did not know what those triangles mean since there is no information on them when you click or hoover.

In your table images i do not see a column by the name = โ€œItemโ€. Were the names changed for the purpose of the post? If so, could you post the ATUAL formula you are using. It is important to know what column you are using to do the LOOKUP().

Yes, the table is in spanish.
Item = Partes
Quantity = Cantidad.
OnHand = Bodega

The problem then is that you are not linking to the correct row to get its Quantity value.

For example you have several rows with Partes column = โ€œTubo 1/2X6 mts Rivalโ€. Which one is it suppose to pick up? I am not sure how LOOKUP() performs in this situation (@Steve??) but i suspect it just returns the first one it finds in the table search.

You need to link the rows by ID.

Ok, Partes is a unique Item and there are no identical Items so I do not use as uniqueid()

Inventory table:

Table to subtract from inventory:

The table to subtract has the Item as ref to the Inventory table.

It is unique in Inventory, but it is not unique in your Sales tableโ€ฆcorrect. In other word, In SalesTable you have multiple rows with the same Item listed.

So lets back up a bit. When you execute this expression below, you are doing this from the Inventory tableโ€ฆcorrect? [_THISROW] is an Inventory Table row?

([OnHand] - LOOKUP([_THISROW].[Item],SalesTable,Item,Quantity))

Also I works without errors on the integer items.

RenatoE
Participant III

Just to add more information. If I only do a few items at a time then the decimals are correct.

RenatoE
Participant III

I found a solutionโ€ฆ It seems the Lookup() function was doing a great job but the table records where not being deleted in time, or deleted in the manner that I thought the software was executing, so it garbed the wrong value. Now I have implemented a different execution where the row that was processed is surely deleted before the next row is processed. But if I have too many records of multiple clients to process the system seems to fail at some point.

I will try to do a flow chart to present the alternative execution.

The original actions shows below:

RenatoE
Participant III

Improved actions where thee delete row function has been moved so that the processed row is deleted before the next row is processed.

RenatoE
Participant III

The proposed solution only works when there are no multiple items with different quantities. For some reason the App still fails to subtract the correct quantities.

Top Labels in this Space