Get the result of an equation that interrogates 3 different tables

[Elodie_Delneuf]

Hello the community,

I am building a platform that includes:

- 1 [Partenaires] : they are shops who issue a commercial offer

- 2 [CSE] : they are companies who contribute to the commercial offer and offer an additional amount to their employees, their employees' invitees and their employees' family (these are 3 categories of beneficiary). The amount of the contribution will vary based on a beneficiary category.

- 3 [Salarié] : they are employees who can benefit from either a commercial offer, or an exclusive offer if their company contributes on the given offer.

I want to created a table where I will calculate the amount due by the employee, based on a) his Employee ID (and the company attached), b) the Commercial offer chosen and c) the beneficiary (the employee himself, an invitee or a family member).

For a chosen Commercial offer, and given the company the employee is linked to, I need to know if there is a contribution from its company. That would be quite easy if there was only one contribution, from one company, for one commercial offer. My problem is that there are 3 possible contributions (for the 3 beneficiaries categories) from one company, for one commercial offer.

So if I put it straight:

- I want to create a form where the employee record ID is prefilled. So the employee is known, therefore I know its company

- The employee would select a commercial offer from the catalogue

- The employee would select the beneficiary of this order (it's one order, so one record and calculation, per type of beneficiary).

From there, I need to :

1) know if there is a special contribution from the company (if there is, I will likely get 3 records, for the 3 contributions per beneficiary that exist)

2) that's my tricky step > extract the one record that matches with the chosen beneficiary 

3) and from there, make my calculation so I can send an email to the employee telling him how much he must pay online via Stripe.

Thanks in advance for your super precious help!

Elodie

[TheTimeSavingCo]

Hmm, could you provide screenshots of your tables with an example scenario and what you'd want the end result to be?  A screenshot of your form would be great too

I'd love to help but it's difficult to visualize your setup, sorry!  For example, I can't say for sure which table has the offers or how it links to the CSE table, and so I'm not sure really clear how the data is flowing you know what I mean?

[Elodie_Delneuf]

Thanks a lot for looking into this with so much attention!

Of course, here is the link to the all base: https://airtable.com/appIwFzX5bYDU9Luq/shroXiapYg3SIsGXS
Only consider the 6 first tables (I rounded in blue)

And attached are the screenshots showing the linked tables.

The 🎫 Offres are the Commercial offers from the shops.

The  🎁 Offres CSE are the additional contributions from the companies.

I would like the end result to be :

- In the table ⚙️ Process Achat Salarié,

- Based on the Employee, its linked company (CSE), and the beneficiary chosen upfront,

- Input (if there is one) the correct additional contribution (= 🎁 Offres CSE) in the column I rounded in orange in the screenshot named 🎁 Offres CSE. So I can then tell the employee how much there is left to pay.

I tried an automation to match employee with 🎁 Offres CSE but that only worked when I had one 🎁 Offres CSE for one 🎫 Offres. The beneficiary dimension (that leads to 3 potential 🎁 Offres CSE for one 🎫 Offres) changes everything.

[TheTimeSavingCo · Accepted solution]

Ahh, thank you for the details!  If I'm understanding correctly, we want to find the appropriate record from "🎁 Offres CSE" that has the same selected "🧍 CSE" and "BENEFICIAIRE" entered in "⚙️ Process Achat Salarié", is that right?

If so, a Find Record action that looks like this should work:

Link to base

The tricky bit is setting up the linked record data, and you'll need to click into the linked record first:

And then select the linked record's ID:

  

[Elodie_Delneuf]

That's it! It works perfectly fine. I added a condition to find the appropriate record from "🎁 Offres CSE" that has the same selected "🧍 CSE" and "BENEFICIAIRE" and "🎫 Offre" entered in "⚙️ Process Achat Salarié"

Thank you so much, you're a star