9 Replies Latest reply on Mar 24, 2013 12:26 PM by MattLeach

    Delete imported records based on record in another table

    MattLeach

      Have a 2 table solution that is setup as follows:

       

      Table 1 = Brands

      Table 2 = Products

       

      The products table is going to be populated by importing a csv file on a regular basis. Once this file is imported, i have a script that loops through the imported records and flags them for deletion if they meet certain criteria.

       

      In the Products table there is a field for brand. What i would like to add to the script is that if the Product's Brand matches a record in the Brands table, flag it for deletion.

       

      What would be the best approach for the above scenario?

        • 1. Re: Delete imported records based on record in another table

          Matt said, "What i would like to add to the script is that if the Product's Brand matches a record in the Brands table, flag it for deletion."

           

          Why not stop them from being entered at all?  You can validate unique on a Brand field ( validate always no User override ) ... or any other validation you require ( with a few limitations ) and records which fail validation will not be imported. 

          • 2. Re: Delete imported records based on record in another table
            keywords

            Another way would be to create a realtionship matching brands---products::Brand = brands::Brand. Then have your looping script look for matches in this TO and flag by your preferred method.

            • 3. Re: Delete imported records based on record in another table
              MattLeach

              This sounds like a better approach. Having not had much experience with validating fields as of yet, do you have an example on how the validation should be setup, by relationship?

              • 4. Re: Delete imported records based on record in another table

                I can only solve subjectively and I do not have specific example (you said, "if they meet certain criteria.") but if you have a Brands table with a BrandID and you only want one Brand to be imported into Products then you create a BrandID (if you do not have one) as standard field (number or text) in Products and populate it and then go to validation tab and set to 'unique', check 'always' and uncheck 'allow user override'  and don't put a message.  If an importing record contains Brand which already exists in Products then that record will be skipped because it will fail validation. 

                 

                Another example is if you have two fields which together should be unique, create a field (regular data) with auto-enter of the concatenation which produces the unique characters and uncheck 'Do not Replace Existing Values' if any.  Set this new field to the unique as described in first paragraph and when you import these two fields, validation will fire.

                 

                If your situation is different please provide the field names and combination that would determine their unique properties for your specific import and we will help you with it.

                • 5. Re: Delete imported records based on record in another table
                  MattLeach

                  This is how things are set up from the previous developer (i inherited file). There is a table called Brands with a single field called brand (Brands::brand). The users create a new record for each brand that they do not want to import.

                   

                  So basically the brands table contains multiple records, all of which they do not want imported.

                   

                  There is a products table that gets populated with the contents of the imported file. In this table there is a brand field. (Products::Brand) If the a product being imported contains a brand that is in the brands table, they do not want it imported.

                   

                  As for the comment about meeting certain criteria, i already have those criteria figured out, it's just this one that was stumping me a bit.

                  • 6. Re: Delete imported records based on record in another table

                    What version are you, Matt?  I created an example in 12 if you want it but basically its like this:

                     

                    Products::Brand = Brands::Brand

                     

                    Then on Brand in Products, apply the strict validation as I've indicated but instead of using Unique, use  By Calculation with:

                     

                    IsEmpty ( brands::brand )

                     

                    Now when you import into the Brand field in Products, those Brands will not import.  One caveat ... it WILL still increment your Products serial numbers but they should be hidden and not worried about.  If you would still like the file, I can attach it (and I'd even re-create it in 11, no problem).  I should always create demos in 11 because they are easy to convert up but reverse isn't true, LOL.  If  I am still missing your need please correct where I'm off. 

                     

                    OH!  And uncheck 'validate only if field has been modified'

                     

                    Message was edited by: LaRetta ... corrected the calculation

                    • 7. Re: Delete imported records based on record in another table
                      MattLeach

                      So basically the end result will be it will import the product record, but the brand field will be empty, correct?

                       

                      I am using FMP 12, i would appreciate the example as im more of a visual learner.

                       

                      Thank you for all of your help.

                      • 8. Re: Delete imported records based on record in another table

                        No the brand field won't be empty - it will not import records from the 'no-brands' list.  Here is an example and you will note that I've highlighted the bad brands in the upper right (that is example of your import records).  The upper left is the bad brands list and below are the Products which will import. 

                         

                        I left the import open so you can step through it to see how it is set.  Note the dialog at the end shows 5 records were skipped from the import and it is the highlighted 5 from the brands list which failed strict validation.

                         

                        Message was edited by: LaRetta

                        • 9. Re: Delete imported records based on record in another table
                          MattLeach

                          Thank you very much for the example file. It makes perfect sense now and i was able to duplicate the functionality to my database.

                           

                          Thanks again!