11 Replies Latest reply on Dec 13, 2016 9:00 AM by David Moyer

    Simple(?) calculation query

    JulianJohnson

      Hi All,

       

      I need to do what I'm sure should be a really straightforward calculation and am having a total brain freeze.

       

      I need to return a carriage delimited list with the same number of lines as I have related records.

       

      So I have a calculated field in my parent table and just say there are three related records in a child table, I just need to return the value of a different field in the parent table three times on separate lines as my result.

       

      I have the List() function, I was hoping I could do with Substitute but don't have a reliable field value to substitute from (unless I just put in a calc always returning the same value). I'm sure there's a way more elegant way to do it!

       

      I'm so sure it's simple but I'm having a complete blank!

       

      Thanks in advance.

       

      Kind Regards

      Jules

        • 1. Re: Simple(?) calculation query
          David Moyer

          Hi,

          I assume that you have empty fields in your child table and the List function doesn't return a blank value for those, and therefore not working for you.  Is that correct?  (sorry, gotta run out - somebody else will help you.)

          • 2. Re: Simple(?) calculation query
            Johan Hedman

            You List ( ) function on your related records

            FileMaker Pro 15 Help

            • 3. Re: Simple(?) calculation query
              philmodjunk

              Like David, I'm having trouble picturing what result you really want here.

               

              I can produce a calculation with the same number of returns (or any other character or string you want) as specified as a number, but you seem to want a field that appears empty but which actually has a list of returns in it and I can't quite see how that would be useful and thus am thinking that this isn't what you really want?

               

              If you want a list of values where missing data leaves a blank spot in the list, I suggest trying ExecuteSQL instead of list.

              • 4. Re: Simple(?) calculation query
                erolst

                Hi Jules -

                 

                as David has already mentioned, List( ) only returns non-empty fields, so if you have three related records and in one of those the queried field is empty, your list will only have two values.

                 

                A workable strategy would be to use SQL with the COALESCE function, like:

                 

                ExecuteSQL ( "

                  SELECT COALESCE ( yourField, 'placeholderValue' )

                  FROM ChildTable

                  WHERE foreignKey = ?

                  " ; "" ; "" ; ParentTable::primaryKey

                )

                • 5. Re: Simple(?) calculation query
                  JulianJohnson

                  Hi All,

                   

                  Thanks for this.

                   

                  To clarify, I need to replace each line in my list with a value from a field in the parent table.

                   

                  So if I have three child records with values:

                   

                  record_a

                  record_d

                  record_j

                   

                  where the value in the field is often different and unpredictable and my field in the parent table is 16:

                   

                  I'd return:

                   

                  16

                  16

                  16

                   

                  I have it working by doing a list based on a calculated field in the child table that always returns a predictable value.

                   

                  The other alternative is to count child records and have a calculation that repeats the value of the field in the parent table (16) three times separated by carriage returns.

                   

                  Not sure if this helps?

                   

                  Kind Regards

                  Jules

                  • 6. Re: Simple(?) calculation query
                    erolst

                    Julian Johnson wrote:

                    Not sure if this helps?

                    I think it would help to know why you are doing this, ie the concrete use case - which may lead to a better approach, or even an altogether different strategy.

                    • 7. Re: Simple(?) calculation query
                      JulianJohnson

                      It's in a system that processes a bill of materials to order components, where a bill of materials can be recursively nested within one or more other bill of materials.

                       

                      I have two virtual lists, one returns the IDs of the bill of materials records, the other (this one) returns the order by week when that component needs to be ordered by. So for a nested bill of materials, it needs to return the same week for each related line item.

                       

                      It works perfectly on a calculated field returning the same value and a substitute, I'm just not sure it's very efficient.

                       

                      Thanks

                      Jules

                      • 8. Re: Simple(?) calculation query
                        erolst

                        You could adapt the SQL example given above to simply

                         

                        ExecuteSQL ( "

                          SELECT ?

                          FROM ChildTable

                          WHERE foreignKey = ?

                          " ; "" ; "" ; "theValueToUse" ; ParentTable::primaryKey

                        )

                         

                        which returns a list of n times "theValueToUse", with n being the number of matching child records.

                        • 9. Re: Simple(?) calculation query
                          JulianJohnson

                          That's great thanks, I will give it a go and see how I get on.

                           

                          Thanks very much.

                           

                          Kind Regards

                          Jules

                          • 10. Re: Simple(?) calculation query
                            philmodjunk

                            And here's a non SQL approach:

                             

                            Subsitute ( 10^NumberOfValuesHere - 1 ; 9 ; YourTable::YourFieldHere )

                            • 11. Re: Simple(?) calculation query
                              David Moyer

                              if it really is just a matter of repeating the same text/number, here's yet another approach, my custom function ...

                              RepeatText ( "16¶"; $repetitions )

                              Call it useful, call it educational, call it a Recursive Function to repeat text

                              As used above, this will leave a trailing ¶.  You can adjust for that.  You can also just use Phil's suggestion (post 10) as long as your list is always shorter than 800 rows.

                              p.s. I'm not trying to poo-poo anybody else's suggestion - I'm just want to share my function.