Returns the result based on two conditions in lookup columns

[leandro_medeiro]

Hello. I have a table that it is filled using a forms. there are two fields coming from two other tables that are selected by the customer.

I need a formula column where it analyzes the two filled fields and returns the result (remuneration) based on a third table (remuneration_table).

below I am attaching a photo to illustrate.

basically I need the formula to return the candidate’s remuneration based on the sector and position he will be hired.

Could someone help me?

[Kamille_Parks]

A formula alone can’t do this. Here’s what I would try:

• Set your formula to concatenate your {SECTOR} and {Attachments} fields together

{SETOR} & " " & {Attachments}

• Ensure the primary field of the RENUMERATION_TABLE concatenates the {SECTOR} and {OFFICE} fields in the same pattern
• Add a field that links the RECRUTAMENTO table to the RENUMERATION_TABLE
• Copy the {RENUMERATION} formula field value into the link field (either manually or use an Automation to do it for you)
• Return the {VALUE} field in the RECRUTAMENTO table using a Lookup field

[leandro_medeiro]

Hi Kamille, thanks in advance for your support.
I didn’t quite understand how step 3 " * Copy the {RENUMERATION} formula field value into the link field (either manually or use an Automation to do it for you)"

could be more detailed for me?

[Kamille_Parks]

Either:

• Copy and paste the value from the formula field into the Link field
• Use an Automation that copies the formula field into the Link field
  • Trigger: When RENUMERATION} field updates
  • Update Record Action: same record ID as trigger, add the Link field, insert the {RENUMERATION} field’s value by clicking the blue plus symbol at the right of the input box

[leandro_medeiro]

I’m copying the result of the formula into the link column but it doesn’t return the data, a new row is generated in the REMUNERATION TABLE table. any tips ?

[Kamille_Parks]

Kamille_Parks:

Ensure the primary field of the RENUMERATION_TABLE concatenates the {SECTOR} and {OFFICE} fields in the same pattern

Did you do this^?

The purpose of copying the formula into the Link field is to connect to the appropriate record. IF its creating new records, that means the formula output a combination of {Sector} and {Office} that doesn’t exist in the RENUMERATION table yet.

[leandro_medeiro]

I understood what happened, is that I wasn’t creating the formula in the primary field. I made the correction and now it worked. I will try to automate the process. I’ll talk to you already.

[leandro_medeiro]

I’m lost in this second part of automation.
I’ve tried in several ways

[Kamille_Parks · Accepted solution]

Click the blue plus button next to {SALARIO 2} to pull in the {SECTOR + FUNCAO} field

[leandro_medeiro]

It worked out! Thank you so much for your help and patience Kamille.