10 Replies Latest reply on Jul 8, 2015 12:03 PM by NiccoDeamus

    Best method of entering multiple similar new individual records from a list in related DB

    NiccoDeamus

      Title

      Best method of entering multiple similar new individual records from a list in related DB

      Post

      sorry if this question is rather stupid...I am very new at this database stuff.

      I have two related files containing records of cows.  one is a record of everything a cow does, the other is its medical history.  occasionally a whole group of cows in a field may have a medical treatment, a vaccination for instance, and this must be individually recorded in the medical record.  

      what would be the best approach to automate this process?  the only variable is the ID of the cow which could be looked up from the field list in the cow records to create a new medical record entry, the treatment in the medical record would be the same for all animals

      many thanks

       

        • 1. Re: Best method of entering multiple similar new individual records from a list in related DB
          philmodjunk

          There are different options and much depends on the design of your database.

          Scripts can loop through records and assign values to fields.

          Replace Field Contents--which can be done manually or in a script can update a specified field for every record in a found set.

          And it's possible to set a value in one field and multiple other fields in the record look up (copy) or link to data in a related table.

          • 2. Re: Best method of entering multiple similar new individual records from a list in related DB
            NiccoDeamus

            thanks for that.

            if i were to use a loop script, how would it run?  if i performed a find in the cow record, could the loop script in the medicine record request the batch medication information then run down the search result in the cow record and create a new medicine entry for each cow?  I think this would be the way to operate the script.

            if this were a good method and i could write a script and assign it to a button to run a batch medicine entry from a pre searched cow record list could you give me an idea of the script components?

            run the script

            enter repeated information (e.g. medicine, dose, date)

            begin loop

            create new record, fetch first/next cow in list on cow record

            enter cow number, enter repeated information

            repeat loop

            __does this seem the right or possible way of doing this job?  and which script functions should i be learning how to use to use them.  I can see that "LOOP" is required.  I am unsure of which other functions would be appropriate to get/lookup the list and the function to demand the repeated info

            many thanks

            • 3. Re: Best method of entering multiple similar new individual records from a list in related DB
              philmodjunk

              You'd need to describe your tables and relationships in more detail.

              Do you have this relationship?

              Cows----<MedicalHistory

              Cows::__pkCowID = MedicalHistory::_fkCowID

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              Then you can define a set of global fields to use to enter the data you want to add to the medical history for each cow thus treated. You would enter the data into these global fields first, then run the following script.

              Loop
                  Set Variable [$CowID ; value: Cows::__pkCowID ]
                  Go To Layout ["MedicalHistory" (MedicalHistory) ]
                  New Record/Request
                  Set Field [MedicalHistory::_fkCowID ; $CowID ]
                  Set Field [MedicalHistory::Field1 ; Table::gGlobalField 1]
                  #Use one set field for each global field. The global field can be defined in any table in your file.
                  Go to Layout [Original Layout]
                  Go to Record/Request/Page [next ; exit after last]
              End Loop

              • 4. Re: Best method of entering multiple similar new individual records from a list in related DB
                NiccoDeamus

                brilliant...thanks.  i have defined the relationship and will play with the suggested script...many thanks

                • 5. Re: Best method of entering multiple similar new individual records from a list in related DB
                  philmodjunk

                  You can put a portal to MedicalHistory on your Cows layout to show a given cow's medical history. If you include a date field in MedicalHistory, you can sort your portal in descending order by that date field to show the most recent entries first.

                  • 6. Re: Best method of entering multiple similar new individual records from a list in related DB
                    NiccoDeamus

                     

                    Hi Phil....thanks for this help

                    I have encountered a couple difficulties.  

                    1. I am assuming that the script runs in the MedRecords layout? I have assigned a button to the script (which does not work, currently only creates a new record)

                    2. I am unsure of the logic...Set Variable sets $ID to CowID but I am unclear why or which list it is/should be pulling the ID from.  I need it to pull IDs from a find list (i don't need the script to do the find)

                    3.  assuming it has an ID from the correct Cow list the script requests a new record, Sets Fields (I set date field to Get(Current), MedRecID to $ID, sets the globals etc)

                    4.  the script then GoTo Layout(Original)  ...  I am not sure about this one.  As I say, the script doesn't appear to get this far, but I am unsure of what this will do, or whether i should refer it to a CowData script that refers to a layout

                    5.  if the script is in the CowData layout with a Goto Layout script to goto the MedRec layout in line 3 it just makes a new empty line in the CowData record

                    I think the main problem is that it is not finding the list of IDs with which to populate the MedRec.  Currently it makes one new record with the previous used ID in the MedRec

                    I should add that because i am so new to this and have no programming experience at all, i have probably made a mess of the databases.  The Cow data and Med records are separate DBs.  i think i now realise that they should be tables in one DB.  The relationships are specified in the relationships graph and data is linked between them and this works.

                    • 7. Re: Best method of entering multiple similar new individual records from a list in related DB
                      philmodjunk

                      1. The script starts running from the Cows layout and switches to a layout based on the related table each time a related record needs to be created before switching back to the Cows layout.

                      2. The value of $CowID is the unique identifier that uniquely identifies each record in the Cows table. This value is called the Primary Key of the Cows table. If you look at the set variable step, it references the value of Cows::__pkCowID. Cows:: identifies the Cows table __pk is my naming convention for primary key fields with the double underscore used to ensure that an alphabetical list of field names from the Cows table will list this field before all others. CowsID tells you again that the value of this field is the unique ID of a record in the Cows table.

                      3. The script creates a new record in the MedicalHistory table.

                      4. Go to layout [original layout] returns the script to the original layout, a layout based on the Cows table. Changing  layouts in order to change table "context" is a common feature of many FileMaker scripts. It can trip a number of script triggers, so this, while the simplest way to do this, must be done with caution if you've set up OnLayoutEnter or OnLayoutExit triggers--to name just a few of the triggers that can be tripped by a script changing layouts.

                      5. Correct, but then the set field steps populate that new record's fields with data. The first set field sets the foreign key field, _fkCowID to the value of the current Cow record on the Cows layout via the $CowID variable that was previously set to this value. Subsequent set field steps--I included just one as an example would be used to transfer data from either global fields or variables to fields of the new record. The precise number of set field steps needed here will depend on how you design your database.

                      I think the main problem is that it is not finding the list of IDs with which to populate the MedRec

                      My assumption here is that you had already pulled up a found set of records on the Cows layout just before running this script. IF there is a found set of a single record on the Cows layout, you'd get exactly one new record in the MedicalHistory table. IF there are multiple records in the found set on that layout, you'd get one new record in MedicalHistory for each. But this requires that you have this found set in place and that you ran this script starting from the Cows layout.

                      It would be simpler to keep the Cows table and MedicalHistory table in the same file, and this was what I had assumed was the case here. You can use Import Records with the "new table" option if you want to move a table and it's records from one file to another. But you can also add a new Tutorial: What are Table Occurrences? in Manage | Database | Relationships that refers to a table from another file and then you can refer to that table occurrence just as though that Table was defined in the current file.

                      • 8. Re: Best method of entering multiple similar new individual records from a list in related DB
                        NiccoDeamus

                        ok...i have imported the databases into new tables.  all layouts etc have left behind.  anyway of importing them?

                        wrt to the resorted lists...i had done that...i will concentrate on getting the pk and fk in the right order and make sure the script is in Cow list layout and referring to the MedRec Table and layout

                        slow progress

                        • 9. Re: Best method of entering multiple similar new individual records from a list in related DB
                          philmodjunk

                          Copying a layout from one file to another is possible but careful attention to detail can be needed if you have buttons and/or script triggers that are part of your layout.

                          See this thread for more on the subject: Importing Layouts

                          • 10. Re: Best method of entering multiple similar new individual records from a list in related DB
                            NiccoDeamus

                            it works...i bow down before you

                            one problem i was evidently having was i needed to actually select the ID field in the Cow layout, so i am unsure of how much head bashing i was doing because of that error

                            many many thanks for your time

                            regards

                            n