Reverse Lookup for reference field

Ask for Help
#1

Hello

Table A : Supplier

  • Period (text)
  • Name (reference from table B)
  • …
  • Global_evaluation

Table B : Anlaysis

  • Period (text)
  • Supplier (text)
  • …
  • Global_evaluation (B_Supplier = A_Supplier, B_period=A_period)

I’m struglling to link B_Global_evaluation to A_Global_evaluation (lookupOne does not work as Supllier is reference field)

How can I do it without reconstructing everything ?

#2

My understanding of your question is that:

  1. Supplier.Name is a Reference type column, with DATA FROM TABLE set to Analysis.
  2. Analysis.Global_evaluation is a Reference type column, with DATA FROM TABLE set to Supplier.
  3. Analysis.Global_evaluation needs a formula so that it returns a reference R to a record in Supplier such that R.Name is a reference to the current record of Analysis being evaluated, and R.Period is equal to Period in the current record of Analysis.

Is that right?

The formula for that is

Supplier.lookupOne(Name=rec, Period=$Period)

rec is a special variable in all Grist formulas that refers to the current record being evaluated in the formula. $Period is actually just short for rec.Period. So Name=rec means looking up a record of Supplier where the reference column Name contains a reference to the record rec in Analysis. You can also write Name=$id, it means the same thing.

2 Likes
#3

Hi thansk and sorry for my poor explanations.
Yes perfect ! (strangely Name=$id is easier for me to understand)

Thanks I’m explorating the way to use grist instead of seatable as a local shared database.
I’ll need some cutom widget but the way it is integrated via public url is a bit confusing for me.

Anyway that’s a promising tool