0

Retrieve a field from one table in another

I have a database dedicated to events. In this database there is a table called “contracts” and another one called “processing” In the first table (contracts) there is a field that indicates the delivery date which is called “delivery date”. I would like to return this field automatically to the “processing” table as well. Obviously the delivery date refers to a specific record that has a name “Name” in contracts table which in the table “processing” is called “job name”. What kind of formula could I use? Thank you!

16 replies

null
    • Fred
    • 3 mths ago
    • Reported - view

    Is there a link to contracts in the processing table?

    Can you list the direction N:1 or 1:N between the tables in events and in processing? When you edit the reference field it shows you the direction at the top of the edit window.

      • giuseppe
      • 3 mths ago
      • Reported - view

      yes there is a link to contract in the processing table. The rest of the sentence I'm sorry but I didn't understand it

      • giuseppe
      • 3 mths ago
      • Reported - view

       I don't know if I understand but I can in the table “processing” retrieve data from the table “contracts”

    • Fred
    • 3 mths ago
    • Reported - view
     said:
    I can in the table “processing” retrieve data from the table “contracts”

    If you want to get delivery date in contracts from processing then something like the following in a formula field:

    contracts.'delivery date'

    assuming you haven't change the name of the reference field to something besides the default contracts.

    • giuseppe
    • 3 mths ago
    • Reported - view

    Unfortunately, it does not work because the records are not associated. I tried. do this formula however there is something wrong:

    let nomematri := 'job name';
    if cnt(select 'contracts' where 'name' = nomematri) then
        'contracts'.'deliverydate'
    end

    • giuseppe
    • 3 mths ago
    • Reported - view

    my mental path is this: search the “contracts” records for the memory where “name” is equal to the name in “processing.jobname” and return me the delivery date

    • Fred
    • 3 mths ago
    • Reported - view
     said:
    Unfortunately, it does not work because the records are not associated.

     How can they not be "associated" when you said that there is a link between processing and contracts?

    Can you upload a test DB?

      • giuseppe
      • 3 mths ago
      • Reported - view

       Yes, sure

      L41 test.ninox
    • giuseppe
    • 3 mths ago
    • Reported - view

    The table in which I would like to insert the field is “Lavorazioni” while the table from which I would like to take the record is “Contratti matrimoni” The record is called “Consegna entro il”

      • Fred
      • 3 mths ago
      • Reported - view

      Lavorazioni is the one to the many of Contratti matrimoni. Which means one record of Lavorazioni can be linked to many records in Contratti matrimoni. You can tell visually if you see a table under the link name or you can see a single line.

      So my question is how will know which record in Contratti matrimoni to show the delivery date of?

      • giuseppe
      • 3 mths ago
      • Reported - view

       The “Lavorazioni” record has a field called “Nome job relativo” which must be identical to a field in the “Contratti matrimoni” record called “Nome matrimonio in contratti”. This way there is certainty that the two records are related to the same job. 
      I know the whole thing is messy and poorly done but I developed this database slowly over several years making changes and additions, forgive me.

      • Fred
      • 3 mths ago
      • Reported - view

       said:
      The “Lavorazioni” record has a field called “Nome job relativo” which must be identical to a field in the “Contratti matrimoni” record called “Nome matrimonio in contratti”. This way there is certainty that the two records are related to the same job.

       Once you link them they are related, you don't need a special field to link them. That is the one big benefit of Ninox.

      Can a record in Lavorazioni be linked to more than one record in Contratti matrimoni?

    • giuseppe
    • 3 mths ago
    • Reported - view
     said:
    Can a record in Lavorazioni be linked to more than one record in Contratti matrimoni?

     yes

    • giuseppe
    • 3 mths ago
    • Reported - view

    thank you! I found the solution and the right formula! I found it because of your advice in another post that I had missed:

    let curRec := this;
    first((select 'Contratti matrimoni')['Nome matrimonio in contratti' = curRec.'Nome job relativo'].'Consegna entro il')

      • Fred
      • 3 mths ago
      • Reported - view

      I would recommend that you use the reference field to Contratti matrimoni instead of doing a select. Reference fields are always much faster.

      first('Contratti matrimoni'.'Consegna entro il')

      select commands are slow and should only be used if there is no relationship at all.

      • giuseppe
      • 3 mths ago
      • Reported - view

      thank you!

Login to reply

Content aside

  • Status Answered
  • 3 mths agoLast active
  • 16Replies
  • 75Views
  • 2 Following