8 Replies Latest reply on May 14, 2013 7:20 AM by mikebeargie

    field to highlight specified record from portal

    Lukian

      I have a portal with a date field and a number field. I am trying to create a field that displays the number from the record with the most recent date where the number field is not empty. Ideas? thanks.

        • 1. Re: field to highlight specified record from portal
          Mike_Mitchell

          One way to do this would be to create another relationship to the child table, where you add a constant calculated field (probably global) to your parent table. That calculated field would be a very small number, like 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001. Then, you add another criterion to your new relationship:

           

               ParentTable::VerySmallNumber < ChildTable::Number

           

          This will cause all records equal to zero in the child table to be filtered out of the relationship. Then, you can sort the relationship by Date (descending). At that point the first record in the relationship will be the most recent date.

           

          (Note that this will NOT work if negative numbers are expected in your child table number field. In that case, additional conditions will be needed.)

           

          HTH

           

          Mike

          • 2. Re: field to highlight specified record from portal
            mikebeargie

            ExecuteSQL() could handle this calculation without adding another relationship. As long as Lukian is using filemaker 12

             

            IE - get the first value from a returned list.

             

            GetValue (

             

            ExecuteSQL(

            "

            SELECT (number)

            FROM childtable

            WHERE childkey = ?

            AND ( number IS NOT NULL OR number =/= '' )

            ORDER BY date DESC

            " ; "" ; "" ; parentkey )

             

            ; 1 )

            • 4. Re: field to highlight specified record from portal
              mikebeargie

              I just wish that ExecuteSQL() supported LIMIT. wrapping it in GetValue() all the time is a pain.

              • 5. Re: field to highlight specified record from portal
                Lukian

                Im having trouble with the Returned List method:

                 

                By

                WHERE childkey = ?

                Do you mean put the relationship criteria between child and parent (ParentID (FK) = ParentID (PK))?

                 

                By

                =/=

                Do you mean ≠ ?

                 

                Thanks Again.

                • 6. Re: field to highlight specified record from portal
                  mikebeargie

                  yes, I couldn't draw out the not equal to sign, forgot the keyboard shortcut. NULL is not always equal to empty or blank, so get in the habit of testing for both in your calculations. I am testing for both by using (    OR    ) in the above example.

                   

                  The question mark acts as a placeholder for a variable that you can pass later. I am passing "parentkey" at the end of the ExecuteSQL() function in the example above. Leave the question mark in place, replace "parentkey" with whatever field the parent key is coming from.

                   

                  You can pass as many variables (in order of question mark in your SELECT statement) at the end of the executeSQL() function, separated by carriage returns, as you want/need to place.

                  • 7. Re: field to highlight specified record from portal
                    Lukian

                    If I use:

                    AND ( number IS NOT NULL OR number ≠ '' )

                    the calculation field does not work (results in ?)

                     

                    If I use :

                    AND ( number IS NOT NULL)

                    The Calculation field works

                     

                    I see why you are using the (  OR  ) but I'm not sure why its not working in the formula.

                     

                    Side Note: The purpose of the number field is to enter raw data, shouldn't it be either null or not null (never empty or blank?) - therefore do I need to have the (   OR  )?

                     

                    Thanks for your Insight

                    • 8. Re: field to highlight specified record from portal
                      mikebeargie

                      I've run into trouble before with NOT NULL / NULL, so I always test for multiple versions in my scripts.

                       

                      If someone clears a value out of the field, it's not necessarily "NULL", it's just empty. If you wanted more insight into this, Beverly Voth is the person who tipped me off to this issue, you might want to track her down to consult with you. When I was running into this issue it was with a fairly large record set, with a ridiculous calculation inside of ExecuteSQL(), this by comparison is simple and you might never need to test for multiple conditions with (    OR    )

                       

                      You might need to replace your not equals sign with either != or <>, I'm not sure if ExecuteSQL respects the symbol.

                      1 of 1 people found this helpful