How to get values from diffrent table in to field?

Hi guys, I am in need of some help with this one.
I am developing an app related to road construction.
There are a few tables below.

  1. master table
    fields >> ID, Date, Road No, Layer Name, From, To, Side, Stock pile No, Sample No, MDD, OMC
  2. Stock pile
    fields >> ID, Sample Date, Sample No, Stockpile No, Material, MDD, OMC

fields >> ID, Sample Date, Road No, Road No, Sample No, From, To, Side, MDD, OMC

The “layer name” in the master table determines whether the data is taken from the stock pile table or the subgrade table.
Suppose we get data from the stock file table.
Then take the stock pile no of the stock pile according to the material you want. The material is filtered according to the layer name of the master table. It happens from the table in the picture below.
The stock pile no should come to the stock pile field of the master form as a dropdown list. When you select an item from that dropdown list, the sample no related to that material should appear as a dropdown list for the sample no filed in the master form.
Then I want to show the MDD and OMC related fields for that sample no.
I tried to get to the place where I select the sample no. But do not get MDD and OMC correctly.
It used the following equation.

IFS([select MDD table]= "stock pile",LOOKUP([Sample No],"stock pile","Sample No","MDD"),[select MDD table]= "subgrade",LOOKUP([Sample No],"subgrade","Sample No","MDD"))

Here is how the app currently works.

Sorry about my poor English ability.

use [_THISROW].
see for explanation:


great :heart_eyes: thank you.
Your answer is correct, the equation for MDD should work correctly.
But when you select the stock file no, how do you make the sample no come to the dropdown list?


Thank you so much for your help. I worked hard for a few hours to fix the problem. For that I used “Valid if” . In addition, “IFS” “select” “_Thisrow” “AND” & “Lookup” was used. Your guidance was very important.