12 Replies Latest reply on Oct 14, 2015 5:44 PM by user19752

    Dynamically assigning database fields to layout objects?

    techxedu

      Greetings!

       

      Thanks to the assistance I've received here in troubleshooting object slide feature, I think I have a draft of a report template. My volunteer project using FMPv12 is student report card so my goal is to have a PDF for each student which is a record in the FMP database.

       

      While searching the knowledgebase I saw one article that pointed out the "ObjectName" property. I'm not quite sure how to address an object by that property in a script yet but at least made sure to name each object I place on the layout with unique names using the Inspector (Inspector -> Position tab -> Name property): "classnameobj", "overallgradeobj", "subcategory1gradelabelobj", "subcategory1gradeobj", etc. etc. Some classes have a subcategory grade on top of overall grade while some don't.


      I've also made sure that fields in the student database contains sequential numbers for field names: "class1name", "class1overallgrade", "class1subcategory1gradelabel", "class1subcategory1grade", class2name", "class2overallgrade". etc., etc.


      Above in the hopes that I can dynamically assign database fields to layout objects while looping through all classes a student takes (i.e. a record contains these). Pseudo code of what I'm thinking is like this:

       

      for x = 1 to report.card::NumberofClasses

      reportcardtemplate.classnameobj=reportcard::classxname

      reportcardtemplate.overallgradeobj=reportcard::classxoverallgrade

      for y =1 to 5

           if reportcard::classxsubcategoryygradelabel <> NULL then

                reportcardtemplate.subcategoryygradelabelobj=reportcard::classxsubcategoryygradelabel

                reportcardtemplate.subcategoryygradeobj=reportcard::classxsubcategoryygrade

           endif

      next y

      next x

       

      Is above doable in FMPv12's scripting? I just wanted to make sure I'm on the right track. If not I'll have to just make the template 10 times longer and larger (if there are 10 classes a student takes) and not dynamically assign the database fields to layout objects but use the "Specify Field" that pops up when you right-click a layout object instead. In all honesty I'll rather dynamically assign through scripting language if version 12 has these capabilities because the template will also be short & sweet.

       

      Thank you,

      Rob

       

        • 1. Re: Dynamically assigning database fields to layout objects?
          vavo

          Hi Rob,

           

          the short answer is: No, that is not possible. Fields on a layout can not be dynamically changed by a script. But they can be addressed and read dynamically (see below).

           

          From what I see in your post (forgive If I am wrong), it looks like you are planing to put all the information in one record. If you haven't yet, you should look into relationships, that is where filemakers power lies.

          So rather than having fields for every potential class a student can take (class1name, class2name etc.) you should create one table for students and one for classes that relates to students.

          Then you can use a portal from students to edit and create records in classes using loops like you suggested above. Portals however are not good for printing, so think about the end result and how that can be achieved early on.

           

          Relationships is the way to go for sure, but there is one more option that you can use for your script suggested above:

           

          The "Set Field By Name (fieldName; value)" script step. You could use a script like the one above and define the fieldname as "class" & $index & "name" where $index increments by 1 with each iteration.

          The equivalent to setting a field, is the "Get Field (fieldname)" script step, which you can use in the same way.

           

          Hope this helps,

           

          Valentin

          • 2. Re: Dynamically assigning database fields to layout objects?
            user19752

            +1 for vavo that creating portal

            I'm not sure for your tables, but usually making fields like class1, class2 ... is not the best way in relational database.

            • 3. Re: Dynamically assigning database fields to layout objects?
              keywords

              Re Valentin's comment: "Portals however are not good for printing, so think about the end result and how that can be achieved early on".  Your best way is to create a separate layout for reporting, based on the child table (i.e. the records in the portal).

              • 4. Re: Dynamically assigning database fields to layout objects?
                techxedu

                Valentin & All,

                 

                Bummer for now 

                 

                Thank you for the relationship tip - I understand what you are saying. I have a relationship between the "student demographic data" table and "reportcard" table using student ID# but that latter table is not well thought out as all of you have noticed. I initially did this as it was the easiest way to import a CSV file at end of each quarter into the reportcard table and since we didn't want to change the front end spreadsheet record entry. Not the


                The concat example of "class" & $index & "name" was what I was planning to use so I can specify the right database field as payload for the layout object that is anchored in the report form. But the "Set Field By Name (fieldName; value) is for changing the value of a field in the database/table but not the other way around where cobgey


                So if I'm not misunderstanding, there is no way to assign a field to an object on the report via script, dynamically & in realtime while looping? But also the opposite, i.e. assign a new value to a field in the table, is supported? The reportcard is for only creating a resulting PDF from records and not inputting or doing lookup working on the screen. I approached it with the mentality of something like a reportwriter but looks like I'm misunderstanding the capability of the report template in FMP?


                Thank you for continued education of me ... worked in relational database decade or two ago from a programming/database engine for applications perspective so trying to rekindle that while helping out. The most recent must have been some version of MS Access which I used their report writer and 'thought" I could do what I was hoping for in FMPv12 ...


                Rob

                • 5. Re: Dynamically assigning database fields to layout objects?
                  vavo

                  I have to admit, I am still not 100% sure I understand what you need to do, but I will give it a shot anyway :-)

                   

                  Take a look at the file attached. Rather than populating report fields with a script it jumps to a report layout showing class records for one student at a time. A script looping through students (is in the file) should get you very close to what you need - I hope...

                  • 6. Re: Dynamically assigning database fields to layout objects?
                    techxedu

                    Thank you vavo - I'll take a look at the attachment when I return home tonight. I'm very interested at what you wrote in the reply about looping

                     

                    I think I'm not explaining well so try once more ...

                     

                    Putting aside the not good practice of the reportcard database fields, my question is whether the objects in a report template can be updated dynamically from database field.

                     

                    Rob

                    • 7. Re: Dynamically assigning database fields to layout objects?
                      techxedu

                      Thank you again vavo for the attachment. I studied it and I see my errors.

                       

                      It is also coming back, albeit a little slowly as I've been away from database for a while, that I do seriously need to think about normalizing the table that contains the imported grades - just like what others who responded to my inquiry has been saying.

                       

                      Meaning ... I need to ditch the idea of dynamic & looping but instead do it through the table relations as suggested. It probably may be difficult for this upcoming quarter-end but I sure want to attain that. For this quarter I probably have to make concessions

                       

                      Rob

                      • 8. Re: Dynamically assigning database fields to layout objects?
                        vavo

                        Normalizing data sounds like a good idea. I  hear you that it might be more work for you right now, but it will pay off.

                         

                        for the looping right now:

                         

                        You can do that using fields that are only used for reporting and then look at these two script steps:

                         

                        - Set Field By Name (FieldName; value)

                        - Getfield (fieldanme)

                         

                        With both of those you can use an index in your loop to define filenames and get and set the reporting fields easily. Be aware that the “getfield” step needs fully qualified filenames, i. e. “table name::fieldname”

                        • 9. Re: Dynamically assigning database fields to layout objects?
                          techxedu

                          That GetField(fieldname) seems useful vavo!

                           

                          Curious, when assigning to a layout object using GetField(table::field) does one have to assign to a special property or just "=" to the object "Name" assigned through the Inspector?

                           

                          I'm assuming the GetField(table::field) is analogous to specifying the "Display Data From" in the "Data" tab of the Inspector.

                           

                          Guess I'll concentrate on normalization A little rusty as I haven't worked with database for a loooooooong time - this must be FUN!

                           

                          Rob

                          • 10. Re: Dynamically assigning database fields to layout objects?
                            techxedu

                            sorry vavo - I think you said "no" to assigning a value to a layout object in the beginning!

                            • 11. Re: Dynamically assigning database fields to layout objects?
                              vavo

                              true... the sad news is, all your work with naming the objects diligently does not really get you anything. All you need is the field names (as displayed in the Display Data from, or when double clicking the filed in layout mode).

                               

                              To use the GetField () function I recommend using a variable to define the name first. makes it easier on the debugging side of things:

                               

                              Set Variable ($fieldname; "reportcard::class" & $index & "name")               //this should  result a value "reportcard::class1name", where $index = 1.

                               

                              Now you can use the field name to actually get the value:

                              Set Variable ($fieldValue; getfield ($fieldname))                                      

                              the value you need is stored in a variable called $fieldValue. Use the "repetition" in the variable if you loop through several fields in one got to create a number of variables. assign $index to the repetition in increment the numbers there. Variables will then be called $fieldValue[1], $fieldValue [2]....

                               

                              Here is a screenshot:

                              Screenshot 2015-10-14 11.46.13.png

                              • 12. Re: Dynamically assigning database fields to layout objects?
                                user19752

                                Not so different, but use merge variable instead of field, you don't need "Set Field By Name".

                                 

                                As of FM13, there is "Hide object when" feature to set variable using Let() function, then you need only set the prefix variable in script.