4 Replies Latest reply on Apr 28, 2010 6:44 AM by LaRetta_1

    Auto-creation of related records

    rgriff

      Title

      Auto-creation of related records

      Post

      I'm having some trouble understanding how to make this work the way I need it to work (FMPro 11) ... or if it can work this way. Here's a subset of my tables and some of their fields:

       

      Table: Sales

      Fields: Buyer Name, Qty, Amount, Country

       

      Table: Countries

      Fields: Country [related to Sales:Country], Count (Sales:Country)

       

      The idea is that the Countries table shows a count of buyers by country...and it does. The problem is that this table isn't updated when I import new data. If the new data has a new country, it doesn't create the required Countries:Country entry, so that it can be summed. As a result, I have to review the imported data for any new countries, then manually create their records in Countries.

       

      I thought the "allow creation of records in related table" would do this for me, but further reading shows that was a bad assumption. So I'm guessing I need to run some sort of a script or something, post-import? In my head, it seems simple, but time-consuming: loop through each record in Sales, doing a find on each record's country against the Countries table. If it comes up with no match, create the record in Countries.


      But there must be a better way, right? All ideas appreciated.

       

      -rob.

        • 1. Re: Auto-creation of related records
          LaRetta_1

          There are several approaches but creating new records in Countries cannot happen while you are importing.  Turn on Allow Creation to your Countries table (from the graph)  and use Allow Creation.

           

          Then your import script could look something like this:

           

          Set Error Capture [ On ]

          Import ( ..... ]

          If [  Get ( LastError ) ] ... something went wrong - halt

          Halt Script

          End If

          Enter Find Mode [ ] uncheck pause

          Set Field [ Countries::Country ; "*" ]

          Omit Record

          Set Error Capture [ On ]

          Constrain Found Set[]

          If [  not Get ( LastError )

          # you now have some new countries

          Loop

          Set Field [ Countries::Country ; Sales::Country ]

          Go To Record/Request/Page [ Next ; Exit after last ]

          End Loop

          End If

           

          UPDATE:  Back up before trying any new process.  This script was written on the fly and not tested.

          • 2. Re: Auto-creation of related records
            comment_1

            Another option: follow each import into Sales by an import from Sales to Countries (mapping Country to Country).

            This requires the field Countries::Country to be validated as Unique, Validate always.

            • 3. Re: Auto-creation of related records
              rgriff

              Both of these solutions are great, but the re-import is more than good enough for my needs.

               

              thanks!

              -rob.

              • 4. Re: Auto-creation of related records
                LaRetta_1

                I did not bring up using an import because I no longer trust or use imports due to the import mapping bug.  I considered it here but I didn't know if there were other fields in Country and some of that field data can be overwritten by sales import if new field is added in Country.  I also was unsure if field names matched.

                 

                I love the theory of 'Add only' with strict validation of unique and I suppose I should have mentioned that option so Rob could choose.  Thank you for bringing it up.  :smileyvery-happy:

                 

                We won't use importing in most tables unless 1) we can always match on names which is only approx 2% of the time or 2) we are SURE that we won't be adding fields in the target tables.  It is simply to dangerous and difficult to administer.

                 

                UPDATE:  In hindsight, it would work fine because you would only be adding new records and NEVER updating existing so you couldn't lose prior data.