1 Reply Latest reply on Aug 1, 2011 12:41 PM by philmodjunk

    Need looping solution

    JimM_1

      Title

      Need looping solution

      Post

      I tried to deal with this in Excel but came up lacking. I’m sure this involves a loop but it’s been awhile since I used a loop at all. I’m using Filemaker 11

      I have an Excel spreadsheet that has about 350 CD Titles. Each CD has multiple tracks from 12 to 100+. So there are approx. 350 CD’s and about 12,000 total tracks (records).

      I have to come up with codes for each CD Title and then insert the code into every record (track) according to what CD they belong to. If I do this in Excel, I can filter by unique records and then manually apply the codes to every first occurrence of a CD Title – and then use drag pasting to get the codes onto all of the tracks. Very time consuming.

      In Filemaker I need to use a loop (I believe) to place a CD code that increments by 001 for every unique occurrence of a CD Title (I’m using ‘XR001, XR002…..XR211, XR212, Etc.). That at least takes care of numbering each unique CD Title. If I can include it in the process, I’d also like to insert the CD code into the appropriate tracks for each CD.

      After I’m done with this I have to export it out to Excel again for ingestion (import) into a SQL database.

      So I need to have some help with a loop as I kinda know how that will work, but don’t have a complete process in hand.

      TIA

      Jim

        • 1. Re: Need looping solution
          philmodjunk

          This can be done without a loop, though that option works also.

          Define an added table with two fields: CDID--an auto-entered serial number. CDTitle--with Unique Values, validate always enabled.

          Import the data first into this table, and enable the auto-entry operations option that pops up in the last dialog you see before the import. This import will load this new table with one copy of each CD Title.

          Now link your existing table of CD tracks to it in a relationship by CD title.

          CD TitlesTable::CDTitle = CDTracksTable::CDTitle

          Define your CDCode field in CDTracksTable to use the auto-enter by calculation field option to use this expression:

          "XR" & Right ( "00" & CD TitlesTable::CIDID ; 3 )

          You can now import the same data into the CDTracksTable, again with auto-enter operations enabled, and the track records will all be assigned the IDcodes you've specified.