1 2 Previous Next 27 Replies Latest reply on Aug 13, 2015 3:01 PM by mrosenhek

    Value List by Calculation

    mrosenhek

      I've attached a file that asks the question and has screen shots.

      Thank you.

        • 1. Re: Value List by Calculation
          Mike_Mitchell

          A couple of ways to tackle this:

           

          1) Create a global field on the parent side of the relationship. Gather a list of record IDs (using primary keys is fine) and place the list in the global field. (Separated by ¶, not by commas.) Create a relationship between that global field on the parent side, and the key field in the target table. (You can include other predicates, if needed.) Base your value list on that relationship.

           

          One good way to collect the IDs is to use the ListOf summary field type. You can use a Script Trigger to populate the global field.

           

          2) Use ExecuteSQL instead of a relationship. It's a little more involved and arcane, but it works. This blog post explains the technique:

           

          FM 12 ExecuteSQL “Unconference” Session | FileMakerHacks

           

          HTH

           

          Mike

          • 2. Re: Value List by Calculation
            erolst

            You got FM13 or 14?

             

            If so, in the Parent table create a summary Field, type “ListOf” for the primaryID field. Let's call it sListOfIDs

             

            In the same table, create a calc field, type text, defined as


            sListOfIDs


            Let's call it cListOfIDs

             

            Add a new TO of the Children table; call it, say, Children_ofParentsInFoundSet, and use it to create the relationship

             

            Parent::cListOfIDs = Children_ofParentsInFoundSet::id_parent

             

            Define your value list using fields from Children_ofParentsInFoundSet, “Include only related values starting from” Parent.

             

            PS: Next time, please try to ask your questions in the post, where you can also insert screenshots.

             

            The less hoops you make people jump through, the more inclined they'll be to help you.

            • 3. Re: Value List by Calculation
              mrosenhek

              Thanks. I will give this a try tomorrow. I really appreciate your help. I’ll let you know how it goes.

               

              I had created a relationship based on AFE and created the value list based on that thinking it would work. It does work but results in ALL of the records with the same AFE, regardless of found set; nisht gut ….

               

              -Moe

              • 4. Re: Value List by Calculation
                mrosenhek

                PS I am using FM14

                • 5. Re: Value List by Calculation
                  mrosenhek

                  Thank you. I'll be working on this tomorrow and will let you know how it turns out.

                  -Moe

                  • 6. Re: Value List by Calculation
                    mrosenhek

                    Hi:

                    I tried this but it did not work. I don't think I explained the situation properly. I'll give a bit of history that might help.


                    I have a parent table TICKETS

                    I have a child table CHARGES


                    The relationship to for this value list is not based on a Parent/Child relationship at all. The only reason the Parent table is even considered is to get the Tickets for the date range the user queries. The script finds all the child records for  date range, that is based on the Parent records. The script is run from the CHILD table


                    The result is a subset of child records that all have Parent records within the queried date range ( date field in the Parent record). The initial method you provided several weeks ago works great. It used what is called the AFE field to group records in a report. Each grouping displays all the "statuses" for that grouping. However, it displays ALL the statuses for that grouping "AFE" not just the found set (based on the parent's date).


                    So when a report is run and even if there is only one item in the grouping, the value list of statuses displayed shows ALL statuses for all records for a given AFE even if they were from tickets not int he date range queried.


                    So lets say we have records in the child record for the following:

                    Jan 1, Status ABC

                    Jan 2, Status XYZ

                    Jan 3 Status PQR


                    But if the query was for Jan 1- Jan 2 only, the list should be:

                    ABC

                    XYZ


                    What it currently show in the report heading is

                    ABC

                    XYZ

                    PQR


                    It's really hard for me to explain this as I do not write very well. It wold be easier in a conversation. Is there any way I can call you? I can't paste a screen shot in this post directly but I will attach it of the report to provide a visual. Thank you.


                    -Moe


                    • 7. Re: Value List by Calculation
                      Mike_Mitchell

                      As I mentioned before, you will need to add additional predicates to your relationship for this to work. In this case, you'll need the join based on the statuses AND the date range. Sorry if "additional predicates" didn't make sense, but that's what it means: More fields in the relationship.

                      • 8. Re: Value List by Calculation
                        erolst

                        OK; what I understand is that you want a list of distinct AFE values for the found set of Charges records (where AFE is a field in Charges).

                         

                        But it is not supposed to a “real” value list that you can apply to a field control – rather, just a list of values.

                         

                        If that is correct**, then you can get that list as follows:

                         

                        • create a “real” value list (in the VL dialog) called “AFEs” that uses values from field AFE

                        • a summary field sListOfAFEs = “List of“ (also) the field AFE

                         

                        Now you have two lists, neither of which gives you quite what you want:

                         

                        • the value list gives you distinct values (an index) for all records of the table

                        • the summary field gives you a dynamic list of values only from the found set (it grows and shrinks along with the found set), but the values are not distinct

                         

                        To get “the best of both worlds”, you can use the calculation

                         

                        FilterValues ( ValueListItems ( "" ; "AFEValueList" ) ; sListOfAFEs )

                         

                        which effectively de-duplicates sListOfAFEs.

                         

                        To get a result string as a comma-delimited list:

                         

                        Let (

                          res = FilterValues (

                            ValueListItems ( "" ; "AFEValueList" ) ;

                            sListOfAFEs

                            ) ;

                          Substitute ( res ; ¶ ; ", " )

                        )

                         

                        but you probably know that old trick already …

                         

                        But here's a new one: if you only need the result for display, you can – in FM14 – use a single segment button bar and mis-use the label calculation for your purposes:

                        Screen Shot 2015-08-12 at 20.08.46.png

                         

                        ** if it is not correct, and you want a “real" VL, then either Mike's or my approach would be appropriate …

                         

                        btw, to insert a screenshot like the one above, use the camera icon in the toolbar.

                        • 9. Re: Value List by Calculation
                          mrosenhek

                          Thank you again. I have to run now but will go over your last suggestions and get back to you. Thank you so much for taking the time to help me.

                           

                          -M

                          • 10. Re: Value List by Calculation
                            mrosenhek

                            Wish I had time to do this now but what I need is a list of STATUS values based on the AFE.

                             

                            ie

                            AFE 1, Status A, May1

                            AFE 1, Status B, May2

                            AFE 1, Status C, May3

                             

                            so if the query is May 1-2, the result of value list should be:

                            A, B

                             

                            As soon as I get back I will work on this again.

                            Thanks

                            • 11. Re: Value List by Calculation
                              erolst

                              mrosenhek wrote:

                               

                              Wish I had time to do this now but what I need is a list of STATUS values based on the AFE.

                               

                              ie

                              AFE 1, Status A, May1

                              AFE 1, Status B, May2

                              AFE 1, Status C, May3

                               

                              so if the query is May 1-2, the result of value list should be:

                              A, B

                               

                               

                              What I see is that the result is based on the date range – unless, as you seem to imply (or I'm merely reading this into your description), you want to get one result for every group of AFE in a sub-summary; so if you had

                               

                               

                              AFE 1, Status A, May1

                              AFE 1, Status B, May2

                              AFE 1, Status B, May2

                              AFE 1, Status C, May3

                              AFE 2, Status A, May1

                              AFE 2, Status D, May2

                              AFE 2, Status D, May2

                              AFE 2, Status E, May3

                              AFE 2, Status F, May3

                               

                              with a query that returns a found set of records for May, 1-2

                               

                              you'd want

                               

                              AFE 1

                                Statuses: A, B

                                <data>

                              AFE 2

                              Statuses: A, D

                                <data>

                               

                              Let me know if that is the case, and I tell you how to adjust the calculation.

                              • 12. Re: Value List by Calculation
                                mrosenhek

                                BINGO!!!

                                You got it

                                That is exactly what I need.

                                • 13. Re: Value List by Calculation
                                  mrosenhek

                                  Here is another screen shot of the report with more data. You are spot on.

                                  Screen Shot 2015-08-12 at 3.21.27 PM.png

                                  • 14. Re: Value List by Calculation
                                    mrosenhek

                                    Hi Mike:

                                    Actually the value list is built through a self join based on only one predicate; the AFE.

                                    The date is only used to get a found set of AFEs for a given date range. The source of the dates, are in a parent records. But the value list is not based on date OR the relationship between the Parent and Child records.

                                    1 2 Previous Next