Products with recipies

[Roman_Lojek]

I have 4 sheets (Order, OrdreDetails, Production,Formulas). I would like to obtain the counted stock.
I have a product list. In the list there are some Main products and components to make the main product(according to recipe).
I would like to get real stock value. For example : when I place an order it takes me quantity on request and shows me the stock with decreased quantities. But when I choose the main product (where I need to build from the components) I would like to see also these components decreased form stock value.

[MultiTech]

Hello @Roman_Lojek

A good place to start when building an inventory system is References:

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

Once you establish a reference connection between tables, AppSheet will create Reverse Reference Lists of all associated orders for a specific product (for example).

From this list of [Related Orders], you can use a List Dereference to pull out the quantity from all those orders:

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.

And if you wrap your list dereference formula in a SUM() formula, you’ll get the total number of items used in all the related orders.

SUM([Related Orders][Quantity])

[Roman_Lojek]

Thanks. That is what I need.
Roman

wt., 15 gru 2020, 16:19 użytkownik MultiTech_Visions via AppSheet Creator Community <appsheet@discoursemail.com> napisał:

[Roman_Lojek]

I have another question.
The solution with dereferenced - works
However I need to use also the spreadsheet formula like :iferror(vlookup(B2;FORMULAS_KOPIA!$A$2:$AD$25;12;0)*C2/100;"")
How to combine them with AppFormula?
Roman

[LeventK]

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

help.appsheet.com

ISBLANK()

Is value absent?

help.appsheet.com

ISNOTBLANK()

Is value present?

[Roman_Lojek]

Thank you Lenvent for your answer.
However I want to make calculations regarding the formulas written in another sheet.
May be I will try to explain.
I have a sheet like Formula with Formula code and quantity.
and 2nd sheet called Recipe
When I chose from Formula the product it shows me in the app the formula taken from the Recipe table.
What I need is to count the components quantities form Recipes regarding the quantity taken from Formula.
That is why I used the formula : iferror(vlookup(B2;FORMULAS_KOPIA!$A$2:$AD$25;12;0)*C2/100;"").
But I cannot use both formulas like app formula and spreadsheet formulas.

[LeventK · Accepted solution]

@Roman_Lojek
How can I offer a proper AppFormula or Expression without knowing anything about your table schema, column structure, column type etc.? Have you checked the pages I have referenced? Your sheet formula is quite simple: You lookup for the value of B2 cell from a table named FORMULAS_KOPIA within a range of A2:AD25 and return the exact match from column index of 12 (which is actually Column L) and do a match operation of multiplying this value with the value in C2 and then divide it by 100. You leave the value as blank if there’s no value matching (in this case an #N/A value).

IF(
	ISNOTBLANK(LOOKUP([_THISROW].[SearchColumnName],"FORMULAS_KOPIA","LookupColumnName","ReturnValueColumnName")),
	(LOOKUP([_THISROW].[SearchColumnName],"FORMULAS_KOPIA","LookupColumnName","ReturnValueColumnName")*[AnotherColumnNameToMultiply]) / 100,
	NUMBER("")
)

Roman_Lojek:

That is why I used the formula : iferror(vlookup(B2;FORMULAS_KOPIA!$A$2:$AD$25;12;0)*C2/100;"").
But I cannot use both formulas like app formula and spreadsheet formulas.

Your AppFormula is above and can you please tell me why you can’t use it?

[Roman_Lojek]

Yes Levent.
the table Formula:
in qty Column I place the required number.
And I want to calculate “qty” form Formula_kopia by qty from “Formula” table and devide by 100 so that it gives me the result in “qty1” of Formula" Table.
the searching table:
Formula_kopia:

[Roman_Lojek]

Dear Levent
Thank u.
It is working now. I missed the column names.
Thanks
Roman

[LeventK]

You’re welcome. Could you please mark my post as the solution (Post#7 to be exact)? Thnx.