6 Replies Latest reply on Aug 20, 2016 11:18 AM by philmodjunk

    Copy values of field n times based on relationship

    answer42

      Hi,

      So, I have table A with a serial number as key field and other metadata on participants of an experiment (sex, age, etc). This table has a one-to-many relationship (based on key field) with a second table B with answers on a diet questionnaire for each participant. I then have a portal on the layout of table A that lets me introduce the answers to each question (for each participant).This works fine.

      However, one of the fields on table B is food item and it will always take the same values (meat, fish, eggs etc). The portions and frequency are the ones that change from individual to individual. I wanted to know if there's any way I can "freeze" this first column on my portal, so instead of always repeating the name of the food item, I can just fill in portion and frequency.

      I thought of writing a script for a button (on layout of table A) that would copy the values of that field (column) of table B every time I changed the record on table A, but I don't know how to go about it.

      Not sure that was clear but I'm quite new to the program and couldn't find similar questions here, so I was hoping someone could help.

        • 1. Re: Copy values of field n times based on relationship
          philmodjunk

          Years ago, I set up a system for a used beverage recycler with a receipt that is "preloaded" with the items most frequently brought in by a customer.

           

          Set Variable [ $parentID, tableA::serialNumber ]

          go to layout ["tableB" ; (tableB) ]

          *set variable [$itemList ; List ( "Food1"; "Food2"; ...)]

          Loop

            set variable [$K ; $K + 1 ]

            exit loop If [ $K > valueCount ($itemList)]

            new record/request

            Set Field [TableB::foreignKeu ; $parentID ]

            Set Field [FoodItem ; GetValue ($itemList)]

          End Loop

          Go to Layout [original layout]

           

          *set variable -- at this point I actually got this list from a table via the list function and a relationship.

          • 2. Re: Copy values of field n times based on relationship
            answer42

            Hello again,

             

            Thanks for your answer!

            But I'm not able to make it work fully (prob because I'm such a noob at this).

             

            I've included questions using * for parts of the script that I don't fully understand but feel free to only answer the question at the bottom, where I address the error I get.

            Changes to the script made by my windows context are in bold.

             

            Set Variable [ $parentID; Value: "tableA::serialNumber" ]*

            go to layout ["tableB" ; (tableB) ]

            Set variable [$itemList ; List ( "Food1"; "Food2"; ...)]

            Loop

              set variable [$K ; $K + 1 ]

              exit loop If [ $K > valueCount ($itemList)]**

              new record/request

              Set Field [TableB::foreignKey ; $parentID ]***

              Set Field [TableB::FoodItem ; GetValue ($itemList)] <- error appears here

            End Loop

            Go to Layout [original layout]

             

            *Since set variable requires a name and a value, I assumed this is equivalent to what you had

            **Could you explain what is this $K variable doing? My guess is that it is stopping the script from introducing the list several times , but I'm not sure

            ***Isn't this replacing the unique serial number for table B with the serial number from table A? Why do I need/want that?

             

            Now for the error, when I try to set the second field , I can "specify target field" ok (although it adds the above TableB::), but when I do "calculated field" with GetValue it tells me that there are "too few parameters" in this function. As far I can tell, I'm missing the valueNumber, specifying which value in the List I want (although I want the full list, so not sure how to go about it).

             

            Hope you have the time to help.

            • 3. Re: Copy values of field n times based on relationship
              philmodjunk

              * There should be no quotation marks visible when you examine this script step in the scripts workspace of a recent version of FileMaker. So remove those if you typed in quotation marks.

               

              **My apologies, I was typing in my iPhone to respond and left out a detail. $K is the loop counter that both serves as an index to refer to a particular value in $Itemlist and to exit the loop when the end of the list has been reached. Line 9, where you have your error message, should read: GetValue ( $ItemList ; $K )

               

              *** I'm working with limited information as to table and field names. I hope that you really haven't named your tables A and B as that handicaps you in terms of not using some of the most basic "internal documentation" available to you for keeping track of what tables and table occurrences do in your file. I used "foreignKey" to refer to the match field in Table B that you have specified in your relationship as the match field to the serial number field in Table A--what we would call the Primary Key in database jargon.

              • 4. Re: Copy values of field n times based on relationship
                answer42

                * There should be no quotation marks visible when you examine this script step in the scripts workspace of a recent version of FileMaker. So remove those if you typed in quotation marks.

                     I haven't typed in quotation marks. They show up after I click okay on "set variable" options  in the following image. No idea how to change that. I'm using Filemaker Pro 12.

                     filemaker question.png

                 

                **My apologies, I was typing in my iPhone to respond and left out a detail. $K is the loop counter that both serves as an index to refer to a particular value in $Itemlist and to exit the loop when the end of the list has been reached. Line 9, where you have your error message, should read: GetValue ( $ItemList ; $K )

                     Changed it, thanks!

                 

                *** I'm working with limited information as to table and field names. I hope that you really haven't named your tables A and B as that handicaps you in terms of not using some of the most basic "internal documentation" available to you for keeping track of what tables and table occurrences do in your file. I used "foreignKey" to refer to the match field in Table B that you have specified in your relationship as the match field to the serial number field in Table A--what we would call the Primary Key in database jargon.

                     Got it. As you can see in the figure, I'm not naming my tables A and B. it was just to make the question clearer.

                 

                The script now works but when I run it the foreign key (Filemaker ID) on table B (Food Freq Quest) appears as ? (and hence the list never appears on the portal in table A (PhD database) layout). I guess because the $parentID variable is not being set properly. Any ideas on how to fix that?

                • 5. Re: Copy values of field n times based on relationship
                  answer42

                  I've fixed it!

                   

                  Thanks for all your help and patience. I was mistyping the value of the variable. Totally my fault it wasn't working.

                  • 6. Re: Copy values of field n times based on relationship
                    philmodjunk

                    Well that's why I said "If you typed in question marks, remove them". If you didn't type them in, nothing to worry about. I knew this was the case in older versions, doesn't appear in 15 so was checking to be sure that you hadn't.

                     

                    A question mark in that context has two common causes:

                    a) a calculation error occurred, but your script steps look perfectly correct to me

                    b) the field is too narrow to display the data. If this is the case, clicking or tabbing into the field will make the question mark temporarily disappear as the field pops out to display its contents.

                     

                    Might your ID be auto entered text in the form of get (UUID )--which produces long strings of unique text?

                     

                    Otherwise, I don't see why you are getting a ? from the script you've shown in your last post.