Find column within a list!

Hi team,
I cretaed a list that takes all data from a table, but there is one data that should be collected from a different table, in builder user should be able to change status to closed "Cerrado" and that status should change in "Order Entries" table, not in "EntregasEntries" table, could you help me do that pls?

Hi @Pabl-2603,

Thank you for reaching out.

Below are 3 tables I am using to achieve what you are ‌trying‌ ‌to‌ ‌do.

1image

OrderDetails‌ ‌(This‌ ‌table‌ ‌records details of orders and includes an OrderID for each order).

2image

Status (Table contains all status values)

3image

OrderStatus - Table has two columns (Rowlink & Picklist format). This table keeps track of status for each orderID.

4image

As shown in below screenshot, I am using a column list to display data from OrderDetails table. Additionally I have added a picklist which gets the data from OrderStatus table.

5image

In below screenshot I am using FINDROW() function to fetch the value of status mapped to this order using orderID.

Below screenshot shows the Display properties of the picklist. This will allow user to change the value of status in picklist and save the changed values to the table.

Finally, this is how it appears in webapp. You can choose and change the status value from picklist.

Hope this will be helpful. Feel free to reach out to us if you have any further questions.

Hi Pankaj,
Thx so much for your help, I just wonder if there is a way I can use my existing tables, in the image below you'll see my data its a little bit different organized.

I have following tables:
Status: this is a row link table (closed and open)
OrderEntries: In here I have table that collects all data when a purshase order is placed, it has a column called "Status" and is in here because there is a way in another app that connects to this one that lets user select only orders in "open" status.

Do you think that can be possible? or should I create another table to keep track of the status for each "Linea de Artículo" ?

When trying to change status in builder it shows error

Hi @Pabl-2603 ,

If I understand you correctly, you wish to eliminate the use of OrderStatus and store the status only in the OrderDetails table. In that case, you can certainly use two tables (Status and OrderDetails) and not use OrderStatus at all. According to your screenshots, I believe each order has an OrderID in the OrderEntries table. There is also a table "Status" which is being used as a rowlink & picklist for the column "status" in OrderEntries.

Assuming that this is correct, you can display (in an editable picklist) the status of each order using FINDROW() as‌ ‌follows:

=FINDROW(OrderDetails,"OrderDetails[OrderID]=%",THISROW()[OrderID])[Status]

I hope this clears up your doubt; please get in touch if you still have questions.

Thx Pankaj,
I think I'm almost there, the app now carries out the order original status (by default is set to open "Abierta"), but then when trying to change it, it does but in the Status "Estado" table and due to that in ends up changing the status of all orders to closed or open depending on what I select, I'm attaching some images so you can understand.

Creating a new purshase order (default status open or "Abierta")

Another user receives the new order and creates a delivery order which takes all info from purshase order and in there user needs to change status to closed or "Cerrado"

When updating status it should do it in below table but it isn't

Hi @Pabl-2603 :slight_smile: :honeybee:

If you're seeing an update across all of the rows in a table when making a change, you may have an automation that is iterating an update on each row. Just to check, are you updating the row with a button or have another automation set up? If so, check to ensure that you are only updating the current row and not iterating over multiple rows. (Multiple rows can changed if you put a formula in the Run options. You want the run options to be blank if only applying to one or the current row).

If you are updating the status only via in-line of the row in the list as Pankaj demonstrated, the FINDROW() formula would only update that row.

Hope that helps! If you're still having trouble, please provide a screenshot of the formula of either the automation or the formula on the status picklist in the list.

Good day Alyssa,
Thx for your kind help, one part of my app lets user grab purshase orders to report a delivery, for example the following screenshot shows the purshase order screen:

Right there, user can create an order that will contain specific data to track not only the purshase order, but also an id for each line that the user is reporting to deliver:

Then when the other company is ready to create a delivery order, they grab those purshase order lines or ids that were created, like this:

The only step that is pending now, is the system to let me change the status to closed or cerrado in spanish and this is important because the way it works is by looking only at open lines or lines where their status is open and is because the user only wants to work on the orders with open status.

Now, right after user has chosen a line to work on, he reports the qty of items he is deliverying and needs to change the status to closed but when he does it, system changes the status of all lines.

OrderEntries table (Status column is a rowlink of Estados table):


In the above table, as I mentioned before, when user is creating a delivery order, system filters lines by open ("Abierta") status.

Then in the below table (EntregasEntries) the info regarding the delivery gets saved

In the delivery screen, user should manually change the status of the line he is reporting to deliver to closed ("Cerrado"), therefore, user won´t see that line in queue anymore

Finally, when user chooses to change status to closed ("Cerrado"), that status should change in OrderEntries Table, since in builder is located the formula that filters orders by open ("Abierta") status

Automation






Thanks for the screenshots, @Pabl-2603 :slight_smile:

Looking at your set up, you'll want to remove the second action of "Update" on the yellow button as shown in your last screenshot (that is the action that is updating the status of all of the rows). If you delete that action, that will fix the issue you are seeing where the status is applying to all of the rows. That way the button only adds the new row of the form (and does not change any other rows).

If you wish to update the status of any other rows, you can do so in-line of the list, by specifying the source it is updating as shown by Pankaj. Once it is set up as shown, the status of the given row will change when a user edits the field in the list (no button needed).

Hope that helps clarify! :honey_pot:

Thx much Alyssa,
I followed your advice but still does not work, pls see what I did:


I'm wondering if mixing two lists in line causes an issue, DATACEL1 belongs to another table ("OrderEntries")

Hi Pankaj,
Any idea as why I get a value error? I'm trying also to get this done with a toggle button which would make the action look prettier but I get the same value error.

Hi @Pabl-2603 :honeybee:

Looking at your screenshots, there appears to be an error in your display tab (under your rowlink and picklist setting). You'll want the formula to match the name of your table (looks like it may be called "Estados", and not "Status"). Selecting the source type as that table would achieve the same result.

Then under the Data tab for your FINDROW() formula, you could then simply put the name of the column ([Status] I believe is what you set the column name too in your table).

Hope that helps simplify! I'll note that if you are still getting errors, you'll want to double check the names of the tables and columns.

Good day Alyssa,
Thx much, I went ahead and change those details but still not blue dot :frowning: , pls look at my screenshots:



OrderEntries table, this is the table with status that should change in builder

Hi @Pabl-2603! We want to provide further support on your use case, and will be in touch separately from this thread. Talk soon :honeybee:

Hi Taylor,
Thx so much, I'm looking forward to working with you and get my app running.

1 Like