I’m working on an inventory app. In a virtual column, I have this formula: Ref_Rows(“Inventory”, “Item ID”) Which is calculating wrong, although the system shows that the formula has no error. This message appears:
“Warning: The argument ‘Item ID’ in REF_ROWS(“Inventory”, “Item ID”) should be the name of a column in ‘Inventory’. If left unchanged, this could result in unexpected behavior.”
The table has the same columns that appear in the formula, but this message still appears.
The result of the formula should be: the Inventory ID, followed by the Item ID and the Data Time. But the result that appears is: the Inventory ID, only.
This error is impacting the result of the Formula: Sum([Related Inventorys][Amount] of another virtual column called Total Stock Available.
Could you please help me with this problem?
Agny_Ferreira:
In a virtual column, I have this formula: Ref_Rows(“Inventory”, “Item ID”)
Is this the system-generated [Related…] virtual column? Did you manually edit the expression at all?
Agny_Ferreira:
Which is calculating wrong
How so?
Agny_Ferreira:
This message appears:> “Warning: The argument ‘Item ID’ in REF_ROWS(“Inventory”, “Item ID”) should be the name of a column in ‘Inventory’. If left unchanged, this could result in unexpected behavior.”
I’ve never seen that before. Can you please show a screenshot of this warning? The expression assistant should know the column names, and it should give you an error, not a warning, if the column name is not found.
Agny_Ferreira:
The result of the formula should be: the Inventory ID, followed by the Item ID and the Data Time. But the result that appears is: the Inventory ID, only.
REF_ROWS() only returns key values, you cannot change this. It appears to be working correctly to me, without any further evidence from you that it is wrong. Perhaps just your understanding is wrong?
Agny_Ferreira:
This error is impacting the result of the Formula: Sum([Related Inventorys][Amount] of another virtual column called Total Stock Available.
Maybe you can show how this part isn’t working?
1 Like
Marc_Dillon:
Is this the system-generated [Related…] virtual column? Did you manually edit the expression at all?
The virtual column [Related Inventorys] was manually edited.
Marc_Dillon:
How so?
It is not returning the expected result.
Marc_Dillon:
I’ve never seen that before. Can you please show a screenshot of this warning? The expression assistant should know the column names, and it should give you an error, not a warning, if the column name is not found.
Sure, follow the screenshot:
Marc_Dillon:
REF_ROWS() only returns key values, you cannot change this. It appears to be working correctly to me, without any further evidence from you that it is wrong. Perhaps just your understanding is wrong?
Maybe, I’m just starting to use AppSheet. I still don’t understand how it all works…
Marc_Dillon:
Maybe you can show how this part isn’t working?
I will send you screenshots in another post.
The formula is resulting in the sum of the entire [Amount] column and presenting the same value for all the [ID Item].I believe this is happening due to the column formula [Related Inventorys].
1 Like
Agny_Ferreira:
Marc_Dillon:> > > Is this the system-generated [Related…] virtual column? Did you manually edit the expression at all?> > The virtual column [Related Inventorys] was manually edited.
You should put it back to what it originally was. Check the version history if you need to. If you can’t figure out what it originally was, change the Ref column in the other Table to Text, save, then change back to Ref and save again.
2 Likes
Marc_Dillon:
You should put it back to what it originally was. Check the version history if you need to. If you can’t figure out what it originally was, change the Ref column in the other Table to Text, save, then change back to Ref and save again.
I found it in the version history. It worked out. Now the formula is working. Thanks for the help!! Thank you so much.
1 Like
What was the original expression? Just curious.
1 Like
The original formula for the Related Inventorys virtual column is: Ref_Rows(“Inventory”, “Name”). This formula impacts the correct result of the Total Stock Avaible virtual column. But the result of the Related virtual column is still not what I need…
I’ll send you a screenshot of the result I need:
1 Like
Agny_Ferreira:
I’ll send you a screenshot of the result I need:
These screenshots are from another app similar to the one I’m working on.
As mentioned previously. REF_ROWS() only returns key values. So to get the result that you want, your key values would have to be equal to the text of the desired result.
I highly suggest not setting your key values to be that, that’s a very long and complicated text string, with lots of special characters. Keep your key values simple.
If you need that result, then add another column in the child table that is a concatenation of the various values into the string that you want, then use List Dereference.
[Related...][new column]
Please review the following two articles, I think you are lacking a lot of understanding in this subject.
1 Like
