0

how to adjust what is returned from a link to another table

Hi,

I have a table with a relationship field (N:1) to a second table.

The relationship field in the first table is set to show the 'Name' field from the second table.

I would like to adjust what is shown in the first table according to whether a separate field in second table (Assessment Date) has been completed. 

So if the Assessment Date is blank, then the relationship field in the first table would show "Waiting assessment". If the Assessment Date has a date, then the relationship field would show Name as normal.

Also, to make it perfect, my second objective is for the relationship field to show "None" where I have not yet linked the relationship field to a specific record in the second table.

I have tried the following for my first objective but it's not working.

let sadrecord := (select 'Client Risk Assessment (trustees)').'Assessment Date';
if sadrecord != null then
    Name
else
    "Waiting assessement"
end

Any suggestions would be great. Thanks!

Mark

11 replies

null
    • mark_robinson
    • 3 mths ago
    • Reported - view

    OK. So I've managed to solve my first objective with this:

    if contains([this.'Assessment Date'], null) then
        "Waiting assessment"
    else
        this.Name
    end

    Does anyone know how I set the relationship field to default to the word "None" where it has not been manually linked to a record in the second table?

      • szormpas
      • 3 mths ago
      • Reported - view

       Hi, regarding your first objective I think the code could be further simplified:

      if not 'Assessment Date' then
        "Waiting assessment"
    else
        Name
    end
    • szormpas
    • 3 mths ago
    • Reported - view

     can you try the following to see whether it works? At the moment, I don’t have access to my laptop to check. 

     

    if not 'PUT HERE THE REFERENCE FIELD NAME' then
        "None"
    else
        if not 'Assessment Date' then
           "Waiting assessment"
        else
            Name
         end
    end
      • mark_robinson
      • 3 mths ago
      • Reported - view

       thanks for your two(!) replies.

      The first one works and, as you say, is simpler. So I have adopted it.

      The addition made in your second reply doesn't work. I've subsequently read that relationship fields don't support displaying text directly.

      I've tried testing this by just putting "None" in the coding area, but the field remains blank. So my second objective might be unachievable - 😪

      But many thanks for your time and input on this.

      Mark

      • szormpas
      • 3 mths ago
      • Reported - view

      , I’ll think about it later

    • Fred
    • 3 mths ago
    • Reported - view
     said:
    I've tried testing this by just putting "None" in the coding area, but the field remains blank. So my second objective might be unachievable

    You are correct if trying to do it within the reference field. Since a reference field shows you data from the linked field, if you don't have a link then there is no data to show.

    You can try the following as a work around.

    1) create the following fields: a yes/no field, call it yesNO, and a formula field.

    2) in you formula field you have the following code:

    if not yesNO then "Click to add" end

    3) in the formula field in the Display if:

    yesNO

    4) in the formula field On click put:

    yesNO

    5) in the relationship field put in the Display if:

    not yesNO

    6) make the formula field the same size as the relationship field and put them side by side

    Now if there is no link the relationship field is hidden and will show the formula field that says Click to add. Then you click on it and it will hide the formula field and show the relationship field.

    You have to click again to make the link, but it does get what you want (sort of). :)

    I was having issues with the On Click working in Firefox on Windows, but it works on FireFox in MacOS.

    • szormpas
    • 3 mths ago
    • Reported - view

    , I tried also myself and I can say that there is not a convesional way to display "anything" into the Reference Field if there is not a link with a record in the parent table (the second table in your paradigm).

    Another workaround (in addition of what  suggest) is to create a dummy (empty) record into the Parent table (your second table). You must check the 'Id' of this record, let's say the Id=10. Then, you can put a trigger and automatically link every new record of the Child table (your first table) to this dummy record. Finally, you can easily apply an "if then else" condition to display the word "None" inside the Reference Field.

    The steps you should follow are:

    1. Create an empty (dummy) record into your second table and copy its 'Id' number (let's say Id=10)
    2. Inside the "Trigger on new record" field of your first table put the code: 'Reference field' := 10
    3. Inside the "Show data as" field of the 'Reference field' put the following code:
    if Id=10 then
   "None"
else
   if not 'Assessment Date' then
       "Waiting assessment"
   else
       Name
   end
end

    What do you think, could such a solution work for you?

      • Fred
      • 3 mths ago
      • Reported - view

      I love all the different solutions that are offered. I like this idea too.

      • szormpas
      • 3 mths ago
      • Reported - view

        it seems that the beach stimulate my Ninox brain!!

    • mark_robinson
    • 3 mths ago
    • Reported - view

    Thanks both.

    I couldn't get Fred's solution to work. But as they are work arounds, they probably over complicate things for me. It was more of a nice to have than essential.

    Thanks again to both of you for coming up with these solutions.

      • Fred
      • 3 mths ago
      • Reported - view

       When you get a chance please mark the post answered. Thanks,

Login to reply

Content aside

  • Status Answered
  • 3 mths agoLast active
  • 11Replies
  • 53Views
  • 3 Following