Need Help with Loop/solution

[Shedboyxx]

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.

So I've created a FM converted file from the Excel spreadsheet with all of the records

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

[comment]

I am having difficulty understanding whether you have one table with all the tracks and CD titles that repeat themselves or 2 tables: one for tracks and one for CDs.

[Shedboyxx]

Sorry I was otherwise occupied J

I’m looking to use one table.

There will be an imported/converted Excel spreadsheet that will in turn be exported back out as an Excel file. Therefore I don’t think having a related table would be the quickest way to put together this solution

This file has preexisting CD Titles and track titles corresponding to each CD title. Each record has a CD Title and track title (amongst other data). The CD Title is repeated for every track corresponding to it.

I want the loop to see a CD Title and then create a 5 character CD Code from a preset (stored?) of 2-3 letters and then insert sequential numbering. That’s the primary goal.

CD Title|CD Code

Meet the Beatles|BTL01

Abbey Road|BTL02

Magical Mystery Tour|BTL03

Would also be great is to have each track corresponding to the CD numbered sequentially as well. Track titles already exist so numbering them should be easy.

CD Title|CD Code|Track title|Track#

Beethoven’s Symphony No. 1|BHV01|1st Movement|1

Beethoven’s Symphony No. 1|BHV01|2nd Movement|2

Beethoven’s Symphony No. 1|BHV01|3rd Movement|3

Handel’s Symphony No. 3|HND03|1st Movement|1

Handel’s Symphony No. 3|HND03|2nd Movement|2

Handel’s Symphony No. 3| HND03|3rd Movement|3

After the last record is set, the loop would exit. Then I’d either script an Excel export or manually do that.

Hopefully this is clearer and any help is appreciated.