If name & date match -> change value

1

Hi guys,

I’m trying to create a control that changes a value if a name & date match, but I can’t come up with the correct formula.

I have 2 tables, Table A and Table B. Both tables have names and dates in them. If a name & date from Table B matches with name & date from Table A, then I want to change a value in Table A to the value from Table B.

An added difficulty is that I’m comparing a single date (from Table A) to a date range (from Table B).

Please see the sample doc below:

I hope someone can point me in the right direction?

2

This formula should do the trick:

[Table A].FormulaMap(
  WithName(Lookup([Table B],[Table B].Name,CurrentValue.Name),TableBFound,
  
  if(
    TableBFound
    and 
    Lookup([Table B],[Table B].Name,CurrentValue.Name).[Start Date] <= CurrentValue.Date
    and
    Lookup([Table B],[Table B].Name,CurrentValue.Name).[End Date] >= CurrentValue.Date,
    
    ModifyRows(CurrentValue,Value,TableBFound.Value),
    _noOP()
    )
  )
)

after the if you see a solitary TabelBFound, which is the same as TableBFound = true()

PS: you probable have to retype the formula - copy and paste is not working for me.

A working sample can be found here: Copy of If name & date match -> change value (which you can copy and open check out the working button.

PS2: _noOP() is undocumented and might stop working someday, but I think it will be with us for a while.

2 Likes
3

Thank you @joost_mineur! :pray:

It makes total sense now that you showed me, but I could not come up with it myself.

I notice you use [Start Date] <= CurrentValue and [End Date] >= CurrentValue . If I understand correctly this checks if the date is between [Start Date] and [End Date]? In other words, I don’t need the date list column? I didn’t know that trick, learned another new thing today. This might come in handy more often :+1:

4

Hey Bas,

I never even noticed the date list column, and now, looking at it, I have to say it is not very practical when dealing with longer periods. When I build a formula I always try to keep in mind that things might change in the future. So, whenever possible, I don’t use extra columns with results that I can generate in my formula.
The short answer: no, you don’t need the Date List column.

Greetings,
Joost

5

I found out yesterday that that is called a Columbus Egg… :wink:

1 Like
6

Hey Joost, I agree, one should avoid extra columns where possible. Now that you’ve learned me this trick I can remove the Date List column. Thanks again!

7

Another thing learned! :grinning_face_with_smiling_eyes: Thanks @Piet_Strydom

1 Like
8

Hi @joost_mineur,

I’ve been trying to implement the formula to my actual doc, but it’s not working unfortunately.

The bottom of the formula window does show the “action” icons, so that made me think it would work. However when I press the button it says “no action taken”.

Maybe it has to do with current value? Its bubbles are white, and not green. Maybe that’s not working because I’ve added a filter before the FormulaMap?

If name & date match -> change value formula
If name &amp; date match -&gt; change value formula1071×1107 100 KB

9

Hey Bas,
You have to copy your doc, clean the tables and enter some dummy information and share it with us in order to help you any further. From a screenprint I can’t troubleshoot (well, I probably could, but I don’t have enough time to reverse engineer your doc).
Or send me a PM to some other options.
Greetings,
Joost

10

Hi @joost_mineur ,

Thanks, I understand. I’ve cleaned the doc and entered some dummy info. Please see below.

The culprit is the orange “button with error”. The 2 corresponding tables are “Planning Taken Rayon 1” and “Afwezig deze week (R1T)”.

I hope you can make sense of it?

11

OK - here you can find a copy of your doc with the button fixed.

Copy the doc in order to check out the changes.

1 Like
12

Thank you so much @joost_mineur :pray: This is a great help.