I have a raw material price list consisting of 2 columns: the raw material name column and the raw material price column. Material A costs 10, material B costs 20, material C costs 25. I have a product pricing table consisting of: column 1 is the product name column, column 2 is the quantity 1 column, column 3 is the price column taken from the raw material table. Column 4 is the quantity 2 column, column 5 is the price taken from the raw material table. Column 6 is the total of column 2 multiplied by column 3 and column 4 multiplied by column 5. So can I get the raw material price in column 3 and 5 in the product pricing table from the raw material price list? and how should I do it? The purpose is that when I change the price in the raw material price list, the corresponding price in the product pricing table also changes.
You can use computed columns like Relation, Lookup, Query for such types of requirements.
You can link Raw Material and Product Pricing tables using a common field.
As I understood from your write up, “Material” field in the “Raw Material” table and the “Product” field in the “Product Pricing” table are common fields from both tables. You can use the Relation column to link both tables using common fields, i.e. Raw Material - Material <—> Product Pricing-Product.
Thank you very much for your response. I have attached specific images to help you understand better. My intention is to have the prices of the product table retrieved from the raw material table. If the prices in the raw material table change, the prices in the product table will change accordingly. I hope you can provide detailed guidance for me. Thank you very much.
How do you know which material goes with which product?
Thanks for sample data providing in tabular format.
- What is the logic of Price 1 and Price 2 ?
- How it can be derived ?
- Is there any common field between these two tables ?
Okay! It is very simple:
1.Add Row ID column to Raw materiall table it is native basic Glide column.
2. Add RawMaterial1ID column to product table
3. Add single relation from RawMaterial1ID(in product table) column to Row ID column in raw material table
4. Add lookup column throw single relation of point 3. and retrive price1(price_per_gram)
5. Repeate 2, 3, 4 for price2
6. Add math column to product table with your formula
I’m quite confused about this issue. Could you provide me with the two tables as images after you’ve done the steps above? Thank you very much.
Oh, you’re absolutely amazing. I just looked at the images and saw that you did and explained everything in such detail, very intuitively. Once again, thank you so much. I’ll give it a try and get back to you later with feedback.
That’s fantastic, I followed your instructions and it worked perfectly. It’s exactly what I needed. This issue has been troubling me for days. You’ve helped me understand Relation and Lookup much better. Thank you so much. I hope to learn more from you in the future.
From your guidance, I see the key point of the issue is to create a Relation for each price column and then Lookup the price based on that Relation. All through the Row ID. When I change the price of raw materials, even the name of the raw material, everything will change accordingly in the Product table. Perfect.
Oh, I’ve had a long-standing issue in Glide. Is there a way to retrieve only the integer in Glide? For example, I have a column with the result being 2.35. I want to create a column next to it that only takes the number 2. Thank you.
You can use a math column with a formula like this to round to a whole number.
Round(X)
Just set the replacement for X with the column that contains your price.
Thank you, your method rounds the number up to 3. Please see the attached image, I just want to retrieve the number 2. In summary, I just want to retrieve the number before the decimal point.
Ah, Ok. Try this
FLOOR(X)
Exactly. Thank you very much. Previously, I had to use INT() directly in Google Sheets, but it computed and responded very slowly.
Yeah, you should move as much computation to computed columns as possible.
When I replace it with computation on Glide, the result returns instantly.