8 Replies Latest reply on Jul 12, 2013 5:30 AM by breezer

    Create list from imported records

    brain

      What's the best method for approaching the following scenario?

       

      I have imported data from an SQL database into a new FMP database and I am stuck on my next step. I've seen lots of different approaches and have been going through videos on Lynda.com but am still coming up short.

       

      I have 60 fields in my table with 80k+ asset records and I'm trying to create a list of the 6k products in those records. Here's a small example:

       

       

      ProductDescriptionManufacturerAsset ID
      Nikon D36 Mpx Nikon CameraNikonND3-005
      Nikon D36 Mpx Nikon CameraNikonND3-007
      Samsung S3Samsung Galaxy S3 PhoneSamsungS3-002
      Kindle FireAmazon Kindle Fire TabletAmazonKF-030
      LG 6560 PKLG 60" TVLGLG-102
      Samsung S3Samsung Galaxy S3 PhoneSamsungS3-024

       

       

      I'm trying to generate a product list like this:

       

      ProductDescriptionManufacturer
      Nikon D36 Mpx Nikon CameraNikon
      Samsung S3Samsung Galaxy S3 PhoneSamsung
      Kindle FireAmazon Kindle Fire TabletAmazon
      LG 6560 PKLG 60" TVLG

       

      I'm not creating the data in FMP otherwise this may be easier to wrap my head around. I've looked at self join tables, ExecuteSQL, aggregate functions, summary, sub-summary, etc. I am completely lost and have spent way too much time trying to figure out this seemingly simple task.

       

      Maybe you can help...

       

      Thank you.

      Brain.

        • 1. Re: Create list from imported records
          breezer

          In your FMP file that you are using to import, change the product field validation to validate "Always" and require "Unique value" toimport_unique.JPG

          • 2. Re: Create list from imported records
            PeterWindle

            You could achieve this by "de-duplicating" records, based on the combination of Product/Description/Manufacturer.

             

            So, what you can do is create a new calculation field that appends these three fields into the one, then running a script that will remove the duplicate records.

             

            The script would be something like this :

            Sort (newfield-calculation of the three fields added together)

            Goto record first

            Loop

            set variable $_info to the calc field

            goto record next (exit after last)

            if ($_info=current calc field)

            Omit or Delete the record

            End if

            end loop

            • 3. Re: Create list from imported records
              brain

              Thank you both for your replies. 

               

              Would you still recommend the same solution if:

              a) this is going to be setup as a recurring report? (I'm taking data from a rental inventory database and creating product forms that are usable for the sales and operations staff.  The export from the internal database will occur daily or as needed)

              b) there are more than 60 fields in the excel spreadsheet I'm importing?

               

              Please let me know if you need more information to understand what it is I am doing.  I tend to be a bit vague because I am such a noob in the world of data and FMP.

              Thanks.

              • 4. Re: Create list from imported records
                Mike_Mitchell

                What is your end goal here? Are you truly wanting to remove duplicates from the data set? Do you want to create a value list with those three elements? Are you trying to set up a list of unique values in a separate table for comparison against an inventory? Or do you just want to create a report?

                 

                We might be able to recommend a method if we knew what the actual end state goal was.   

                 

                Mike

                • 5. Re: Create list from imported records
                  brain

                  Hi Mike,

                  Great questions.

                  What is your end goal here?

                  I want to create a product brochure like this:

                  Sample 1.png

                  From an excel spreadsheet like tihs:

                  Sample 3.png

                  Are you truly wanting to remove duplicates from the data set?

                  Not necessarily, I'm trying to create a product list (1 product) from an asset list (quantities of 1 product).  It would be much easier if I had a parent table of Product IDs but I'm trying to minimize the interaction between export out of the R2 database and import to FMP since this could be a daily recurring import.  I'm thinking daily imports so I can track asset locations with accuracy.

                   

                  Do you want to create a value list with those three elements? Are you trying to set up a list of unique values in a separate table for comparison against an inventory? Or do you just want to create a report?

                  I don't think I'm shooting for a value list.  From what I can tell I don't think that's what I'm going for.

                   

                  Ultimately, this will be a read-only database since I won't be creating anything but layouts with FMP, the data lives in R2.

                   

                  Does that help clarify the purpose?

                  Thanks for your time.

                   

                  Brain.

                  • 6. Re: Create list from imported records
                    PeterWindle

                    Not sure if you'll be able to get exactly the same layout style, but you should be able to come close using the technique I mentioned to get the "short" list of products, then simply create a label layout for the products to dislplay. I'm sure there are other ways of acheiving the same result, but I think this would be the simplest.

                    Omitting the "duplicates" from the list is step 1, then printing/pdf-ing the list from a label layout is the second part.

                    • 7. Re: Create list from imported records
                      Mike_Mitchell

                      Brain -

                       

                      Peter's looping method would work fine. You could also build a value list from the concatenated field. This automatically omits the duplicates. Then, loop over the value list using the ValueListItems ( ) function and create the new records in a separate table. Or, use the Virtual List technique on the results of ValueListItems; this would likely be considerably faster.

                       

                      HTH

                       

                      Mike

                      • 8. Re: Create list from imported records
                        breezer

                        Brian, you could simply import into the FM table as I had previously suggested. Your import table field for the "Product" should be set as "Unique value" . Try it out, you'll be amazed at how simple it is.,

                         

                        Thanks,

                         

                        Tom