1 2 3 Previous Next 35 Replies Latest reply on Aug 9, 2012 9:14 AM by philmodjunk

    Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...

    Mingrl88

      Title

      Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work, No?

      Post

      I was wondering if anyone understood the details of importing? What I mean by that is I have been importing from an excel sheet, right now I just need one column with numbers to import. However I need these numbers, once in the table in FileMaker to be immediately copied over to another table in the same database. I have tried "set variable" and "Set field" on object and layout scripttriggers and it seems to me that when you import it is all one "copy" "paste" montion so if you have a script trigger it won't be trigged because the records arn't being generated one-by-one. Is this correct? 

       

      If so perhaps you might have a suggestion as to how to make essentailly a copy of the imported field - but to a table that is not accessable. Essentially I am trying to have a catalog numbering system for my records that crosses a number of tables. I am doing this using an ID table which pulls the catalog number from one table into the other based on a "lookup" field setting. This of course only works when a field in the ID table matches a field in the other table. It has gotten very confusing having multiple types of ID numbering systems (one for the individual record, one for the record number in the ID table, and then a catalog numbering system that crosses multiple tables) so hopefully this questions is clear enough, but if not please just ask me what needs clarification! 

       

      Many thanks!

        • 1. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
          philmodjunk

          The records are generated one by one, but script triggers are tripped by interaction with a layout and this does not happen during an import.

          YOU can, however, run the same script in a loop that loops through your newly imported records. If you import by selecting import from the File Menu, you can do so to import your records and then click a button or select the script from the scripts menu to run it. If you use a script to import your records, you can add steps to that script that do this.

          • 2. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
            Mingrl88

            That is really helpful information thank you. If I wanted to create a loop script that essentially said "If field x has text" "then set field y equal to field x"  "but if field x is empty" "then stop script" "repeat" How do you write this? Specifically I am looking for the function "field has text" . 

            • 3. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
              philmodjunk

              I would use this script:

              Go to Record/Request/page [first]
              Freeze window
              Loop
                IF [Not IsEmpty (ImportTable::Fieldx ) ]
                    Set Variable [$ValueToCopy ; value: ImportTable::Fieldx ]
                    Go to layout [Select layout based on other table ]
                    New Record/Request
                    Set Field [OtherTable::Fieldy ; $ValueToCopy ]
                    Go to layout [original layout]
                End If
                Go to Record/Request/Page [Next ; exit after last]
              End Loop

              • 4. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                Mingrl88

                Phil,

                The script worked pretty good, after running it all the records with Catalog Numbers was copied over to the other table. For some there was no Catalog Number so it didn’t copy over and thus the primary key connecting the two tables was off, but I could probably fix that is I didn’t have a bigger problem. As it turns out, it didn’t fix any of the actual problem which is that my ID Number table is separate so that it can cross different tables BUT if the institution already has ID numbers then they will want to import them and due to the way FM imports they would need to import twice, once to get all the data into one table and then again to import the ID numbers into the ID number table.

                I thought I could fix this by having the customer import the ID numbers into the same table as the metadata and then just click a button to run the script you just showed me to update the ID number table. Unfortunately this isn’t good enough because A it would need to be constantly updated, and B my “next ID number” system fails. What I mean by this is the way I have it set up is so that if the customer wants the next number, regardless of what material's layout/table they are in, they can click a check box "next ID number" and it runs a script that says, if "Next ID Number" is yes then set field in the ID table to the “Next ID Number” (from a “Next ID Table”) and then set the field in the Next ID Table to the Next ID Number+1. As you can probably guess the new ID number won’t show up in the material’s layout because the field isn’t from the same table.

                So the way I see it, either I take out the “next ID number” system and just have a layout somewhere that displays it and then they enter it manually and this leaves the ID number table open to being incorrect if not updated, or I have to create an interface for having customers import into the ID table - which means they are going to have to import twice for each material type. Both of these aren’t great answers because they mean adjusting the entire database and user interface which is ultimately what I am trying to avoid.

                I realize this is a lot to take in but you seem to understand FM really well so I thought if anyone could help me it would probably be you. I appreciate it greatly! 

                • 5. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                  philmodjunk

                  I need an overview of your tables and relationships. When I try to follow your description of the problem I get lost because I don't have that basic road map to refer to.

                  • 6. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                    Mingrl88

                    Phil, I'm sorry this is pretty complicated so I understand the confusion. I attached a print screen shot that might help. The relationship that is being explained there is the primary key between the ID Table and the Mineral Table (this is a database for geology). Really this all comes down to the fact that tables only populate other tables when there is not only a primary key relationship but also data that matches. In this case that data is in the fields labled "ID Record Number". When the two ID record numbers match everything works, but when they don't then the whole system falls apart, and it is falling apart currently because I only want the customers to need to worry about importing once for each material but that isn't possible if the ID Number fields and "Next ID Number" script is linked to the ID table. I listed below the 3 criteria I am trying to make work. I can get them to all work on their own but not together! I'd really appreciate any idea right now... my brainstorming has just been taking me in circles!

                     

                    1-Import all data including metadata and ID numbers at one time

                    2-Be able to click "next ID number" and have it populate the ID Number field, or a field that the customer can see to identify the next number

                    3-Have the ID Number show up on the Minerals Layout as well as in the ID table 

                    • 7. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                      philmodjunk

                      When the two ID record numbers match everything works, but when they don't then the whole system falls apart

                      That's what should happen! Wink That's how relational databases are designed to work. What you have to do is design your system to build those links by putting the correct matching values in the fields of the correct records to make this work.

                      I'm guessing that you are importing data into the Mineral Table. Is that correct?

                      If so, Mineral Table::ID Record Number should be an auto-entered serial number. I don't think you need the ID table at all.

                      It would be helpful to see all the relationshiip graph. Most of what you have posted is hidden behind the edit relationship dialog.

                      PS. Use the "delete" option that you can select in Edit Relationship with extreme caution. Misuse of this feature can result in cascading deletes that delete far more data than intended when you delete a record.

                      • 8. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                        Mingrl88

                        Well the ID table is what is linked into the "Next ID Number" script so that I can get the next ID number no matter if I am on the rock layout, mineral layout, or any of the other 7 materials, it is also linked to loans and lots. I attached a new print screen shot, it is impossible to fit it all onto one screen, but I tried to get the tables that we are talking about. Hopefully this helps. Thank you!

                        • 9. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                          philmodjunk

                          Ok, with that explanation, I understand the logic, but your design has numerous issues.

                          More questions:

                          What "7 other materials" are you referring to? (I only see a "Rock" and a "Mineral" table.) It sounds like you should have one table for all 7 materials with a field in that record that identifies the material type.

                          Why do records in completely different tables need ID numbers that are unique from all the records in the other tables. ID numbers normally need to uniquely identify records that are part of the same table only. I suspect that the ID Number represents an identifier that you use for labeling/cataloging the physical item. If so, this should not be used as the key linking your other fields in relationships.

                          And there is a way to set up a relationship to that Next ID table so that a simple script generates an ID number for the related record in another table "on demand", but first we need to unravel these relationships.

                          no matter if I am on the rock layout, mineral layout,

                          Hmmm, do you know that layouts and tables are not the same thing even though they may have identical names? You can have a rock layout, a mineral layout plus 5 more layouts and all can refer to the same table.

                          I think you need to provide a description of the data you are importing into your database and what results you need once you have imported it.

                          ARE you importing all your data into the Mineral table or are you doing something else?

                           

                          • 10. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                            Mingrl88

                            Haha, alrighty I think at this point there isn't any harm in giving you all the information... 

                            The database application you have been so gratiously helping me with is called EGEMS. EGEMS is an acronym for "Electonic GEological Management System" and I designed it as part of my masters thesis at Virginia Tech last year. It allows individuals and institutions to manage all of their geological materials (Rocks, Minerals, Fossils, Research Products, Current Equipment, Historic Items, and Library and Archive Materials) in one user-friendly program. Since EGEMS had such a psitive responce from geological community, with a little encouragement I decided to create a business from which to market EGEMS. It has been super slow getting off the ground but a breakthrough came when I was hired at a museum to implement EGEMS for their geological materials.

                            This past week I began the migration/import process and that is where I ran into trouble. I quickly realized that while I might be able to import all the metadata and catalog numbers etc into EGEMS my customers can't. A "Full User" in EGEMS is restricted to only a certain amount of the tables, fields, and layouts that I have set. Since you can only import into the table the current layout is derived from, my customers can not currently import catalog numbers because they can't access the ID Table.

                            To solve this problem I thought a solution might be to make a catalog number field in each of the material's tables and then just have a script copy them over to the ID Table. This doesn't work completely though because although the user can import their catalog numbers at the same time as all the other information, they can't click "next ID number" and get the next number in line, and if the catalog number changes the ID Table doesn't know unless it is re-updated, which means the linked tables "Lots" and "Loans" will not work correctly. 

                            Hopefully this provides a much clearer picture, I wasn’t hiding this from you before I just wasn't sure you needed/wanted to know all about EGEMS so I stuck to the developmental questions. To answer the questions you posed, I listed the 7 materials in the first paragraph, and yes that kind of table is what the ID table is, but instead of “material type” it fills in next ID number. Though you may be onto something there if I could make a script that fills in that field based on the layout that is open. Though this still wouldn’t work for importing…hmm

                            You are right that there are a few ID numbers but each has a purpose. First there is the record number for the individual material. So for minerals that is "Record Number M", these do not cross into other material's tables. The second type is the "ID Record Number". This is a number that autogenerates in each of the different material's tables, and if there is a trigger from these tables (like the "Next ID Number" script) then it also autogenerates in the ID Table. That field is what is used to link the different material types all up to the one ID Table. Though now that I think about it I’m not sure why this is working since the “ID Record Number” is going to increase by 1 in the ID Table so the second record in Minerals would have ID Record Number “2” in the Mineral Table but not necessarily in the ID Table, maybe this is part of the problem… something else is making the ID numbers work together and it’s not what I originally thought?!?

                            The third ID type is the catalog number, This is what the ID Number field in the ID table and "Next ID Number" script are for. Once all the catalog numbers that were already used in the collection are imported, then each time a user makes a new record they have a chance of selecting "next ID number" which runs the script and gives them the next number in line (this comes from an unlinked table called "Next ID Number"). Once the catalog number field is populate it generates the “ID Record Number”. These three ID numbers when correctly working together are the foundation to EGEMS.

                            EGEMS has 57 tables with many fields in each table and then as you said, multiple fields from different tables in the layouts. This is a really complex program but when done right it works great! I just need to figure how to get it back to that... As for your last question users would import their data into the 7 main mateiral tables, and then any additional importing (documents and images etc) would need to go to the appropriate layout for that. Right now the only table they need access to that they don't have already if the ID table. As I mentioned one solution to all this is just to create a new layout with the ID Table as the primary table and let them import into there. I am looking for an alteantive soultion to that though because that method leaves a lot of room for error. I attached my thesis to this post. I can't explain much more than this without re-writing it. Pages 15-33 are the user interface, so that is probabably all you would be interested in. The only thing to keep in mind is that when I wrote the thesis I had only created an EGEMS that ran on FileMaker Pro, now that I have created stand-alone runtime solutionsI have made a few changes. 

                            I also realize we may be more productive if we could just openly talk to one-another. I have skype if you would like to try that. I am in VA and any time after 5 would work for me. I feel alright posting during work hours since I am trying to fix this issue but I would prefer to keep skyping till after hours. If you are uncomfortable with that then no worries I can keep posting on here, I just thought I might offer it as a suggestion!


                            • 12. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                              philmodjunk

                              Since you can only import into the table the current layout is derived from, my customers can not currently import catalog numbers because they can't access the ID Table.

                              This is only true for imports initiated by selecting Import Records from the File menu. A scripted import is not limited in this fashion. It can import data into a table that does not have any layout associated with it at all--though for development and maintenance purposes, I'd have at least one hidden away.

                              To solve this problem I thought a solution might be to make a catalog number field in each of the material's tables and then just have a script copy them over to the ID Table. This doesn't work completely though because although the user can import their catalog numbers at the same time as all the other information, they can't click "next ID number" and get the next number in line, and if the catalog number changes the ID Table doesn't know unless it is re-updated, which means the linked tables "Lots" and "Loans" will not work correctly.

                              I think you are confusing the user of a "catalog number" with a Primary key. Primary keys need only uniquely identify the records in the table where they are defined and can then funciton effectively in relationships. In FileMaker, such a primary key is usually created with an auto-entered serial number. An externally produced catalog number can be imported into a data field and used in searches and sorts, but should not be used to link to other fields in a relationship.

                              Assuming that your catalog numbers are strictly numeric (Many aren't), you can, after import, determine the largest catalog number imported and store it as either data or a "next serial value" in the field options of an auto entered serial number.

                              Note that this has all been posted without reading your PDF document. The interface is actually NOT the key element here. It's looking over your tables and relationships. I'd set up a scripted import system that imports all the user's data into a single table where an auto-entered serial number field auto-enters a serial number to uniquely identify each items. This table would receive imported data for every record in your "7 tables". Then additional imports--all via script would import this data in the 7 separate tables, but scripts would link the imported data in your first "central" table via the auto entered serial number generated during import. Any externally produced catalog number values would imported strictly as data.

                              Frankly, I think resolving the import challenge here could be a major project. I can imagine encountering situations where each institution that wants to use your system has their existing data structured differently with ensuing technical challenges to resolve to pull the data into your solution.



                              • 13. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                                Mingrl88

                                I thought about scripting the imports, I have a script that runs an upgrade that is essentially a whole bunch of imports, but as you said each person is going to have their own file and I'm not going to be able to write a generic enough script. In reading your last post though you made me realize something which if you can help explain more then maybe I can fix this! 

                                What I realized is that I didn't think I was using the catalog numbers as primary keys, but maybe I am! I thought the primary key was the "ID record Number" field's (hence why each table has one linked to the ID table) but as I said before my system for that doesn't hold up. The ID record number fields won't always match because they each have a different abount of records (mineral table has 1000 records but rocks only has 200..). So maybe I havn't gotten the hang of the primary key yet. I thought that was set up through the relationship on the ERD diagram, but maybe I'm wrong? 

                                I really appreciate the feedback, thank you!

                                • 14. Re: Copying Fields -Importing Combined with "Set Variable" or "Set Field" Scripttriggers Should Work,...
                                  philmodjunk

                                  Apologies. I thought ID record number WAS the catalog number.

                                  I do see what looks like confusion over primary keys and I suggest that you rename some fields to avoid the confusion.

                                  Take this field Mineral table::Record Number M

                                  I'd rename it as Mineral Table::__pk_MineralID

                                  pk identifies it as your primary key field and the two underscores in front of the p insures that this field will sort to the top of the list when you sort by field name--a very common order in which to find your fields listed in FileMaker.

                                  Link it to other tables (and name the match fields) like this:

                                  Mineral Table::__pk_MineralID = Citations Table M::_fk_MineralID

                                  In the citations table, the match field is a foreign key, not a primary key and the single underscore means that it will sort to be just after any primary key field defined in the citations table.

                                  All __pk fields should be defined as auto-entered serial numbers. All _fk fields should be defined as number fields--No autoentere serial number settings for them.

                                  While a primary key must have a unique value for each record in the table. Foreign keys do not and in fact usually have multiple duplicate values throughout the table. Example: You may have one Mineral Sample logged in as ID: 23456. There could easily be 5 different Citation Records with an _fk of 23456 as all of these citations are linked to the same Mineral Sample.

                                  Whether or not you choose to rename any fields, check the logic on how PK and FK fields are defined and used to see if this might be why you are having trouble with your primary keys. I'm now guessing that you are trying to use the some foreign key fields (say Citations::Record Number M, for example set up to auto-enter a serial number when they should not do so.

                                  1 2 3 Previous Next