Skip to main content

Array and formulas

  • March 12, 2016
  • 6 replies
  • 41 views
B

Hi,

I have two tables linked to each other by a 1 to n link.
Table 1 can have multiple linked records in Table 2.
In table Table 1 I have a lookup field from Table 2.
Of course when I have multiple records in Table 2 linked to one record in Table 1, I see all the values in the lookup field of Table 1.
Now I’d like to be able to extract one element (the last one, assumption being that the last one belongs to the last created records in Table 2) from the lookup field.

Anyway I can achieve that?

Cheers,
Berni

      This topic has been closed for replies.

      6 replies

      C
      • Christopher_Bis
      • Participating Frequently
      • April 4, 2016

      I also have this problem. Bernard_Toure - I use a LEFT and FIND formula to get the string up to the first comma in the looked up field - but there isn’t a reliable way to control which appears first…

      M
      • Mike_Cassidy
      • New Participant
      • August 14, 2016

      I am looking for the same thing as well. In my case Table 1 holds company information (many companies) and Table 2 holds inspections that either pass or fail (all inspections for all companies by date). I have a roll-up field in Table 1 that collects the most recent inspection date (MAX date) from Table 2 but I want to also show the field with the pass/fail value for that specific inspection date for that specific company from Table 2 in Table 1. My purpose is to be able to have a view of Table 1 that shows only the companies that have passing inspections based on their most recent inspection date. Any help would be appreciated.

      N
      • Neal_Patil
      • Participating Frequently
      • August 15, 2016

      I am looking for the same thing as well. In my case Table 1 holds company information (many companies) and Table 2 holds inspections that either pass or fail (all inspections for all companies by date). I have a roll-up field in Table 1 that collects the most recent inspection date (MAX date) from Table 2 but I want to also show the field with the pass/fail value for that specific inspection date for that specific company from Table 2 in Table 1. My purpose is to be able to have a view of Table 1 that shows only the companies that have passing inspections based on their most recent inspection date. Any help would be appreciated.


      @Mike_Cassidy

      I am trying to put your requirements in pictures.
      Please provide your feedback regarding it.
      Thanks
      Neal
      NealPatil @ gmx.com

      I will put a total of 4 pictures with 1 picture per post because of uploading restrictions.

      There are Companies.

      N
      • Neal_Patil
      • Participating Frequently
      • August 15, 2016

      @Mike_Cassidy

      I am trying to put your requirements in pictures.
      Please provide your feedback regarding it.
      Thanks
      Neal
      NealPatil @ gmx.com

      I will put a total of 4 pictures with 1 picture per post because of uploading restrictions.

      There are Companies.


      @Mike_Cassidy

      There are Inspections.

      N
      • Neal_Patil
      • Participating Frequently
      • August 15, 2016

      @Mike_Cassidy

      There are Inspections.


      @Mike_Cassidy

      I have combined two pictures into the following.
      Thanks,
      Neal
      NealPatil @ gmx.com

      M
      • Mike_Cassidy
      • New Participant
      • August 15, 2016

      @Mike_Cassidy

      I have combined two pictures into the following.
      Thanks,
      Neal
      NealPatil @ gmx.com


      Neal,

      Your pictures are what I am trying to get at. After I posted my question I did come up with a solution that seems to work but it may be a bit cumbersome.

      Table 2 inspections - What I did in my inspection table (Table 2) is created a formula field (Max date failed inspection) that looks at the pass/fail field for that inspection and if it “failed” it records the date of the failed inspection. That gives me a field I can sort for MAX date on from the company info table (Table 1).

      Table 1 Company info - In Table 1 I already had a roll-up field that captured the MAX Inspection date for the company (most recent inspection) from Table 2. I then created another Roll-up field in Table 1 to sort MAX date on failed inspections from Table 2. Still another field I created was a formula field in Table 1 to compare the Max inspection date field (table 1) against the Max Failed inspection date field (table 1) and if there is a match in the dates it enters a “Failed” value and if it doesn’t match it leaves the field blank.

      Not sure if there is an easier way or more direct way of doing it but that seems to work. Still testing it though.
      How where you proposing to accomplish the results in your picture?

      Terms & ConditionsAccessibility statement