Modify Rows by Matching Lookup Values / Inventory Management / Action Modify Row .Filter

My scenario is as follows:

I have Table A with two rows whose values are:

  • Banana
  • Orange

It uses a second column to tell me the number of each fruit I need

I have Table B which has two columns

  • Quantity (a number)
  • Fruit (a lookup field listing Table A fruit option)

I wish to add TableB.Quantity to TableA.FruitNumber of the appropriate fruit chosen from the lookup field TableB.Fruit. I would think that a button with the function of Modify Rows>Filter>+TableB.Quantity would work, but it does not. Can anyone share some formula that I can use to tackle this.

Here’s the formula that i’ve tried - it gives me no errors, but will only alert me “No action taken” when I press the button.

ModifyRows(Filter(TableB, thisRow.[Fruit]=[Fruit Type]), TableB,[Number of Fruit], +thisRow.[Quantity])

Hello,

Did you ever manage to find your solution?

Thank you

Nope. here’s the other 20 characters needed to post this

1 Like

Hey @P_C –

To add the values from B to A — look at the ADDING THE TWO button. Try this:

ModifyRows(thisRow, thisRow.[Fruit Number], [Table B].Lookup([Table B].Fruit, thisRow).Quantity + thisRow.[Fruit Number])

To replace the values: her are a few options.

Option A:
I dont think this is the most efficient formula, however, this formula works for a button:
FormulaMap([Table A], WithName(CurrentValue, [Fruit A], ModifyRows(CurrentValue,[Fruit Number], [Table B].Filter(Fruit.[Fruit Type] = [fruit a].[Fruit Type]).Quantity)))

Option B
You could also create another column in your Table A that is a reverse lookup to the quantities set in Table B. From here you can have the button update the Values from the reverse lookup setup.

Option C
Or add a button to each row.

3 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.