Update a table row based on a condition

1

Hi,

i need assistant in the following case.

I got two tables.

Table1 (The selection table)

InventoryNr | Description | AddToTable2
30, 31        Donuts        Button
30, 33        Bagels        Button
32            Cake          Button
33            Baguette      Button

“InventoryNr” is a dropdown that holds all “OrderNr's” from “Table2”

Example for the first row
30, 31 Donuts Button == Put Donuts to "Overview from Table2" where OrderNr == 30 , Put Donuts to "Overview from Table2" where OrderNr == 31

Table2 (The results / view only table)

OrderNr | Overview
30        Donuts, Bagels    
31        Donuts    
32        Cake    
33        Bagels, Baguette

“Overview” row is empty and should be updated depending on the selection of “Table1”.
The row must display single items (e.g. Cake) and also multiple items (e.g. Donuts, Bagels).

“OrderNr” row is prefilled and not empty

For the button:

FormulaMap(
  thisRow.InventoryNr, 
  If(Table2.OrderNr==CurrentValue,Table2.ModifyRows(Table2.Overview,thisRow.Description),"")
)

Formula explanation

  • each inventorynr from table1
  • if table2 ordernr == current value (inventorynr)
  • modify overview in table2 row where ordernr == current value

got no errors in the formula but receive after execution
Unable to execute invalid action

Thanks for any tipps!

2

Hi @Stefan! A sample doc would be useful for me!
Can you share one? :slight_smile:

3

@Mario Sure :slight_smile:

4

Hi @Stefan,
I tried to provide an alternative implementation: tell me if it easier to think from that perspective.

I have a question, though: Overview is replaced anytime with the value from the triggering row.
Are we sure this is the actions you wish to have? :thinking:

5

Hi @Stefan and @Federico_Stefanato :slight_smile:
I think that it’s a problem of multiple currentvalue, i didn’t study yet the new formula that should make you able to circumnavigate this issue, but you can try with that, or we can look for alternative solutions (also called workaround…)

6

@Mario, I agree.
It’s still not clear the actual data model and the use case.
I have the feeling that this solution is “hiding” a potential different (and easier) approach.

Said that, fully with you.

1 Like
7

@Mario, @Federico_Stefanato

I want to “feed” a table with different types of data.
Instead of “OrderNr” you can use “Weeknumber” for a better understanding.

For example in weeknumber 30 we deliver “Donuts” and “Bagels”.
In weeknumber 31 we only deliver “Donuts”.

Thats why i need multiple values for each row.
If you have a better solution let me know.

Hope this helps.

8

@Stefan oh well there are any type of workarounds, you can treat it like a text and then after copying it you can re-create the correlation with a formula, or you can use multiple row, one for each weeknumber and use another “canvas” button to press the others (just the one you need using a filter), and so on :slight_smile:

9

In general yes. If we have multiple items it should be comma separated. But i am not sure if this is possible.

10

Hi @Stefan,
I tried to build up a very basic implementation of orders management in your document.
I’m not sure this is going to the right direction and if this mimics the actual needs you have.

Let me know if this is close: in that case, I think that reporting information, can be easily derived from the underlying data model.

11

I have seen your extra page right now maybe this will be okay for my implementation :+1:
I just need to simplify this.

1 Like
12

Feel free to change it according to your needs and to ask specific questions

1 Like
13

So…the RowID is necessary right?

14

Nope… not at all.
It’s just a convenient way to identify an order.

15

@Federico_Stefanato

okay i changed it for my case now. Would you have a look on it? Also avoid empty row’s :slight_smile:

You see i need to add for every single item which has the same weeknumber an extra row. But if there is no other way then leave it as it is.

16

Thanks @Mario and @Federico_Stefanato for your feedback. For me the topic is solved i got everything i need :slight_smile:

2 Likes