2 Replies Latest reply on Jul 9, 2015 10:42 AM by dgardner362

    Conditional Format based on the last related record

    dgardner362

      Title

      Conditional Format based on the last related record

      Post

      I have a table of job sites, I have another related table of jobs.  When there is a new job at a job site they are linked using the job site number so when you look at a job site record you see all of the jobs that have been sold at this site from newest to oldest through a portal.  New jobs have not been assigned a warranty date yet because they are not complete.  I would like to use conditional formatting to show a text notice on the main page of the site when there is a job in progress.  This is easy to determine because the warranty date on this related record will be empty.   I have not been able to come up with the correct conditional formatting formula to say if the warranty date of the last (first in portal because of sorting) related job is empty.   Any assistance appreciated.   FMP13

       

       

        • 1. Re: Conditional Format based on the last related record
          philmodjunk

          Normally, you'd use Last() to check the value of a field from the last related record, but Last will not return a null value. It's defined to return the "last valid non-null related value".

          IsEmpty ( GetNthRecord ( Jobs::WarrantyDate ; Count ( Jobs::JobID ) )

          will be the last related jobs record's warrntyDate field is empty. If you need to reverse the logic, use Not in front of IsEmpty.

          but you can also define a sort order in the relationship to make the most recent Job the first related record rather than the last. That would remove the need for the GetNthRecord function call. This could also produce a portal that lists the most recent jobs first and that is often a better order for portals that list some kind of "history" type list of entries.

          • 2. Re: Conditional Format based on the last related record
            dgardner362

            Perfect!!!!   Worked like a champ.  I was indeed trying the Last() method.   Thank you