Show another sheet column in Form

Hello, I hope someone could help me. Here is my problem:

I have a delivery App, I have one sheet for PRODUCTS (contains Product, Description, Kilogram Price, Unit Price, etc), one sheet for ORDERS, and one sheet for ORDER DETAILS (in this sheet I have all the products that belong to the order).

My ORDER tab is a Detail TAB, it contains all the orders of the current signed in user. When you clic an order it shows data from the order and also an Inline List pointing to a relation to the ORDER DETAIL to show the products. Also I have an “Add Product” button that calls a Form to write data in the ORDER DETAIL sheet.

In that Form I have a Choice component that shows all the products of the Vendor, this choice component points to a relation column, this relation columns matches all the products that belong to the Vendor.

So, when the user selects a product (from my choice compoent) I need to show information from the PRODUCTS sheet like product Price, BUT the product Price belongs to my PRODUCTS Sheet. My question is, How to show a column of another sheet in my Form? I only need to show the price, it won’t be a text entry column.

Any ideas please?

Here is my app if you want to look at it:

Thank you

Let me understand @AlfredoZGC

Your 2 choice components are inside a Form?

Does the 2nd choice component depends on the 1rst one? Are they dynamic and in cascade?

Hola Gustavo, I saw you were using the app, I was making changes to the App so maybe it didn’t worked fine

In the FORM that writes Data to the ORDER DETAIL sheet i have only one choice component, the user selects a product from that choice component.

I need to show aditional data like Product Price for example

In that Form I need to show aditional data from the Producto I’ve selected from my choice component

You’ll need to create a relation in one way or another to that other sheet, then create a lookup column pulling in the desired values.

Thank you @Robert_Petitto I’ll give it a try and let you know

@Robert_Petitto it dind’t worked. I’ve created a relation column in ORDER DETAILS to match my column Product with the Column Product in the PRODUCTS sheet. The problem is I’m opening the Form from the ORDERS tab, this Form can only use column components from the ORDERS sheet, and if I try to insert a Text component the lookup column doesn’t appear. This won’t work because I’m in a Form inserting data in ORDER DETAILS sheet, the data doesn’t exists yet, I need to find another way

Thanks any way

I gotcha. Didn’t realize that the text you wanted to display was dependent on the user’s selection. I don’t believe this is possible.

What I would try in that case is to have a separate sheet, let’s say we call it “Ordering”.

You can make all the relations you want, have the user’s choice written to a user-specific column in that “Ordering” sheet. Return all the relations you desire (price, description) etc. Then have a “confirm your order” button that links to a form.

In that form, you can show a basic table containing all the information you want the customer to review before submitting an order, while recording all their choices from the previous screen to the fields you want using “Columns”.

I hope it helps.


Thank you, I’ll try to do this

1 Like

Ok, I´m back… sorry, I was working as teacher-father today!!
What you want to do is to have a relationship in a Form. Depending on the product name, the relation would find addional data associated to that product, right?

Unfortunately, the relations don’t work inside Forms … its is a straight kick between legs, I know how it hurts, I had that pain before :pleading_face:

The plan B is what Thinh told you, this method works because I´m using it on my new APP but it has some manual work and one must be careful in details.

No me vayas a odiar, tú sabes que la verdad a veces duele :rofl:

Chao @AlfredoZGC

1 Like

@gvalero Yes, that is exactly whats I was trying to do. Definitely is a straight kick between the legs hahaha.

I was thinking about what Thinh told me, It sounds good but I will have to change some things in my App.

Also I was thinking, what if I create a PRODUCTPRICE sheet?, then I will be able to relate my ORDER DETAILS sheet with my PRODUCTPRICE sheet. Of course I will fill data in this new sheet, something like this:

Unit  Product     Price
KG   Product1   10
GR   Product1   20
KG   Product2   20
UN   Product3   15

So If I choose Product1 in the Form I will be able to show an Inline List pointing to this new relation between ORDER DETAILS and PRODUCTPRICE. Maybe this could work, I don’t know yet.

Jajajajaja es cierto, la verdad duele, especialmente despues de haber avanzado tanto en mi App para despues tener que modificarla

Chao @gvalero

Hello @ThinhDinh I have been triying to do this but I think it will only work if the user add ONE product to the order. The user have to click on the “Confirm your order” button for every product he want to add, is that correct?

You want all items to be on the same row, right?

@ThinhDinh , No, I want all items in a different sheet. I have one sheet for ORDER and one sheet for ORDER DETAIL (there I have all the items for the order).

I have been modifiying mi app, I made what you told me and now I see it its a better way to do this, thank you very much!

One question please, I have some problems with the user specific columns. This columns are very unstable, for example I have this:

Those 3 columns are user specific, but they keep changing values and erasing. Maybe you know what the problem is?

You have only one screen for ordering? How about setting up an “add to cart” button on every item’s individual screen? Wouldn’t that be better?

It would be great, but that “add to cart” button wouln’t know to which order add the product. I would have to create first the order an then in the item individual screen a choice componen to select the order.

My question was about the user specific columns, why are this columns so unstable?

When do they change values? If it’s a user-specific one that should not have happened.

This 3 colums are user specific

This is an example:

As you can see I first select Pizza as producto and when I’m on the Form the product get changed

That is indeed very strange. Can you please check my personal message?

1 Like

It´s what I call a ping pong effect. I have seen this before when an APP has multiple choices and relations

Here you can see this annoying effect

If you select an option, the relation works but there is a delay to read/write the final value and meanwhile, the old value comes back and changes the screen. I have thought it could be a failure caused by internet speed (sometines the ping pong effect appears at beggining and later, everything works fine).

@ThinhDinh (or anyone else) could you check it out this behavior using my demo APP indicated above? Just to discard an internet issue.

The plan B would be to call @Mark to understand this effect and find out a workaround.

Thanks y saludos para todos!