Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4832 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
Posted

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

Posted

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.

  • 1 month later...
  • Newbies
Posted

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.

This topic is 4832 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.