I have table “Database” with columns “Item code”, “item name”, “quantity” etc.
The second table is “Shop” with columns “item code”, “quantity” etc.
I need to reference the list of quantities while item codes match each other.
Appsheet does not let to use list expression in app formula if column type is “ref”, so I
tried the formula in “Valid if” field.
select(
Database[quantity], [Item code]=[_thisrow].[Item code]
)
Unfortunately nothing happens. “quantity” column in “shop” table is still empty, nothing gets referenced.
Hi @Krismar
I think there is a confusion: you have “quantity” both in Database and Shop.
Can you try this:
[Related SHOPs][quantity]
Please note this expression should be used in the table Database.
This assumes the column “Item Code” in the SHOP table is set with type Ref and data source Database.
1 Like
Where do i use this expression in “database” table?
Shouldn’t it be other way around cause i want to pull the data from Database to Shop?
In a virtual column.
For reference: Use virtual columns - AppSheet Help
I don’t think so, but I may be wrong. You will benefit from reading these short pieces of documentation.
I strongly invite you to read it 
References between tables - AppSheet Help
Dereference expressions - AppSheet Help
1 Like
Yes i have read and watched all these videos for hundreds of times, seems that im just too dumb understand anything at all. Guess i should take a break from this stuff, it gets more confused than clear.
Thanks for your help Aurelien!
1 Like
Okay i think Dereference expressions is the thing i need right now. Watched the video.
I have “item code” as ref column in the “Shop” table. Now the Item code column acts like a door between these two tables right. Now i want to pull the quantity of an item from database to the shop with the following expression: [Item code].[quantity]
This should use the column “item code” to ref to the other table, take the quantity of an item and bring it back to the “shop” table..right? Now how do i make it match the rows cause every item has it’s code and quantity?
1 Like
Hi @Krismar
If you have “Item code” as a ref column to database, just leave it that way.
It will be a drop down.
Now, if you wish to retrieve the quantity: use [Item code].[quantity] in the Initial value expression of the column “quantity” in the Shop table.
1 Like
its quite the task, feel like the help barely relates to my situation the majority of the time.
But from my understanding is that you want the ref_row to essentially be filtered by other values in the table.
you can accomplish this in the actions section under behavior tab.
lets start with generic labels Table 1 has virtual column ref_row of table 2
create an app:go to another view action select table 1.
the target formula will be LINKTOFILTEREDVIEW("Name of a view that contains the ref_row column(if all you want is the ref_row column, or dont have one then create one) ", [COLUMN name you wish to use as filter] = [_THISROW].[same as the other column name])
Maybe im wrong and this isnt what you want, but it will be helpful with things in the future.
edit: The most efficient view to use is a detail view for table 1 and table views for table two.
heres an examp of mine with 5 different reference rows that use [team] as the filter.
1 Like
It is exactly as you described but “quantity” column is empty in the app.
I am currently in a table view, not sure if it matters.
I’ve come up another solution, i actually made virtual columns and this way it seems to work nicely.
Is it possible to ref a value entered in a form view?
I have other table where i fill the form page if I need to add/remove something from my stock.
for example if i remove 10 pieces of a product, can i copy over that quantity “10” via action to another table?
1 Like
Thanks for the help but I actually wanted to pull certain information from table1 to table2, in table2 i use app formulas to calculate the material cost and whatever is left has to be sent back to table1.
Just to be clear, with a short example.
Let’s say you have this:
TABLE 1 (table of ITEMS): key-column ITEM ID, other columns: name, price, initial_quantity
TABLE 2 (table of MOVES) :key-column MOVE ID, other columns: ITEM ID(Type: ref to TABLE1), quantity
You may have:
- in TABLE 2 (table of MOVES) : a virtual column “cost” with expression:
[ITEM ID].[price]*[quantity]
- in TABLE 1 (table of ITEMS) : a virtual column “current_quantity” with expression:
[initial_quantity]+SUM([Related TABLE 2s][quantity])
Yes, that is it. You actually made my own formula better with that example.
Now i just need another piece of data from table3 (when form is saved) and add it to the formula to get the correct “current quantity” value into the database.
1 Like
I’m glad that helped you.
With no further indication, I won’t be able to help you : my magic crystal ball is broken.
Nonetheless, I’m sure you have enough information now to solve your situation.
Let us know if you need more help.
Thanks a lot!
This would be easy if i had to do the calculation only with 1 row/1 item.
I see now that I did not think it through. I need the value entered in a form to be copied over to many rows. My idea is to track the quantity of many items and then calculate the quantities after i have “used” those items. What i mean is that i have “product sets” that consist of different pieces. I need to calculate all those pieces that were used to make a product.
Marc advised me to create a recipe table but i do not understand how to use it.
1 Like
I might be missing the mark, but if you were to creat a table of the product sets(assuming they can be used as a unique key, then a table if the sets with the number of peices like this
| sets | piece 1 | piece 2 | piece 3 | | set 1 | 10 | 7 | 3 | | set 2 | 6 | 4 | 1 |
Then another table that is set up the same with the values used.
Then in you product sets table you can ref_row both of these tables and do math stuff if you want.
