3 Replies Latest reply on Jun 26, 2014 11:45 AM by philmodjunk

    Auto-populate portal rows with new records?

    indyj

      Title

      Auto-populate portal rows with new records?

      Post

           Hi all,

           I am new to FM, so I hope this is not too stupid of a question: 

           I am trying to populate a portal based on one table with a new record for each non-empty field from another table, but I can't figure out how to do it. 

           In brief: I have a database of skeletons and each skeleton is identified by a unique SkeletonNumber (the main DB key).

           I have a table called SkeletalInventory, which has fields for each existing bone of each skeleton - they contain entries such as "Humerus Left", "Humerus Right", "Mandible", and so on (there are 81 fields in this table - 80 inventory fields and the key field). All the inventory fields are displayed on a layout. 

           On the same layout, I have a portal to the table "BoneElements". This table only has two fields: "SkeletonNumber" and "BoneElements". They are related through the "SkeletonNumber" field. 

           Now, I would like to auto-populate the portal with a record/row for each field in the SkeletalInventory table that is not empty. In other words, if skeleton 1001 has entries in the fields for "Humerus Left" and "Mandible", I would like the portal to display two rows like this:

      SkeletonNumber       BoneElement

           1001                            Humerus Left

           1001                            Mandible

           Is this possible? 

           BTW, the reason I need this is that I want to make a value list based on the portal, so that a drop down list in another table only displays the existing bone elements of any given skeleton. For example, when I go to the Pathologies layout (which has the SkeletonNumber field from the base table in the header), I have two fields: "Pathological Bone Element" and "Description". If I were to go to SkeletonNumber 1001, I would like a value list for the field "Pathologicall Bone Element" field that only displays "Humerus Left" and "Mandible". This seems to be working when I enter the values in the portal manually, but I was hoping there was an automated way. Any suggestions are welcomed!

           Thanks!

        • 1. Re: Auto-populate portal rows with new records?
          philmodjunk

               Once you have this data copied over, you don't really need the individual fields anymore. If you are entering measurements or some other data into these fields, this data can also be put in a field in the BoneElements table.

               The following steps can be performed manually or in a script.

               On a layout based on SkeletalInventory, enter find mode and put a single * operator into the "humerus Left" field and perform your find. You now have a found set of all records in SkeletalInventory where this field is not empty.

               Go to a layout based on BoneElements.

               Do an import records that imports data from SkeletalInventory into BoneElements. Use field mapping to map only the SkeletonNumber field between the two tables.

               Import your records. This produces a found set of BoneElements records created by this import. Use Replace Field Contents to put "Humerus Left" into the BoneElement field of every record in this found set.

               Repeat this process but with a different field in SkeletalInventory each time.

          • 2. Re: Auto-populate portal rows with new records?
            indyj

                 Thanks for the quick reply!

                 While waiting, I think I sort of figured out a roundabout way to do the same thing, and I would appreciate any comments on whether or not my way will work too or if it will be problematic somehow.

                 I want to be able to update the value list whenever the SkeletalInventory is added to, so I don't want to do a one-time import, but perhaps I can import over the previous records with a script too?

                 Also, the way I did it requires a layout, since it references the portal, so it may not be the most elegant way, but I was planning to not have the layout show in the menus. 

                 The way I did it was to name the portal object, and then perform this script:

                  

                 Go to Object [Object Name: "BoneElementPortal"]

                 Go to Portal Row [Select; First]

                 Insert Calculated Result [Select; BoneElementsPortalTable::BoneElements; If (not Is Empty (SkeletalInventory::Frontal_Left) ; GetAsText (SkeletalInventory::Frontal_Left)]

                 Go to Object [Object Name: "BoneElementPortal"]

                 Go to Portal Row [Select; Next]

                 Insert Calculated Result [Select; BoneElementsPortalTable::BoneElements; If (not Is Empty (SkeletalInventory::Frontal_Right) ; GetAsText (SkeletalInventory::Frontal_Right)]

                 Go to Object [Object Name: "BoneElementPortal"]

                 Go to Portal Row [Select; Next]

                 Insert Calculated Result [Select; BoneElementsPortalTable::BoneElements; If (not Is Empty (SkeletalInventory::Parietal_Left) ; GetAsText (SkeletalInventory::Parietal_Left)]

                 ....and so on, until all the fields in the SkeletalInventory table were referenced. I wrote "First" and "Next" rather than "Last", because I wanted the portal records to be overwritten when updated and not appended to the end, creating duplicates that would clunk up the database (although it doesn't really matter for the value list it seems, as it filters for unique records?)

                 Then I attached the script to a button in the pathologies layout, named "Update Value List"

                 This seems to be working the way I want, in that the value list can be updated whenever new bones are added to the inventory. 

                 Will this work, or will I run into problems? Is there anything I should add to the script?

                 Thanks!

            • 3. Re: Auto-populate portal rows with new records?
              philmodjunk
                   

                        I want to be able to update the value list whenever the SkeletalInventory is added to, so I don't want to do a one-time import, but perhaps I can import over the previous records with a script too?

                   My recommendation is that you move the data into this related table exactly once and then remove these fields from SkeltalInventory as they are now redundant. The related table of data will be much more flexible to work with.