How to reference a list that matches the specific row

[Krismar]

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.

[Aurelien · Accepted solution]

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.

[Aurelien · Accepted solution]

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.

[Krismar]

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?

[Aurelien]

---

@Krismar wrote:

Where do i use this expression in "database" table?

---

In a virtual column.

For reference: Use virtual columns - AppSheet Help

---

@Krismar wrote:

Shouldn't it be other way around cause i want to pull the data from Database to Shop?

---

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

[Krismar]

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!

[Dohman89]

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.

 

 

 

 

[Krismar]

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. 

[Aurelien]

---

@Krismar wrote:

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])

[Krismar]

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. 

[Krismar]

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?

[Aurelien]

Hi @Krismar 

---

@Krismar wrote:

Now i want to pull the quantity of an item from database to the shop with the following expression: [Item code].[quantity]

---

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.

[Krismar]

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.

[Krismar]

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? 

[Aurelien · Accepted solution]

Hi @Krismar 

---

@Krismar wrote:

Is it possible to ref a value entered in a form view?

---

Yes

---

@Aurelien wrote:

References between tables - AppSheet Help

Dereference expressions - AppSheet Help

---

---

@Krismar wrote:

for example if i remove 10 pieces of a product, can i copy over that quantity "10" via action to another table? 

---

You can achieve that using an action:

Actions: The Essentials - AppSheet Help

[Aurelien]

I'm glad that helped you.

---

@Krismar wrote:

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. 

---

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.

[Krismar]

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. 

[Dohman89]

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.