Calculated Field for Multiple File Nos.

[roxannep]

I work in Access, but have a request from my kid's HS band teacher and this needs to be done in Filemaker Pro. (Reminder to self: DO NOT VOLUNTEER to help "Create" a database until I know for sure what software they are using!)

We volunteers are building a sheet music database (I am the only one experienced on PC's let alone with dbases). The band teacher wants the music indexed in one database, and the catalog number for the hard copy of the music to be:

Band Type
Band Code - File No.

Example:
Pep Band
PB-1

Problem: There are 5 band styles I'm indexing and each style will have record "1" through whatever .... PB-1, CX-1, JB-1

I need a way to have FM store the last used index number for a particular Band type, pull it into the main catalog based on the band type that's been selected upon a new record entry, and produce then produce a "File No." that is then printed by the volunteer on the hard copy of the music and by which it is filed in the library.

I thought about a file outside the main records file that lists the Band type, and the corresponding code and an index number, but how to do I get the right band type to increase by one after each new record is created using that band type.

Example:

I enter the titles/composers of 10 pcs of music in random order (10 records): 4 for Pep Band (PB), 1 for Christmas (CX), 3 for Jazz (JB) and 2 for Concert Band (CB). Ideally, the records would show:

Title 1, Composer, Pep Band, PB-1
Title 2, Composer, Jazz Band, JB-1
Title 3, COmposer, Jazz Band, JB-2
TItle 4, Composer, Concert Band, CB-1
Title 5, Composer, Pep Band, PB-2
Title 6, Composer, Pep Band, PB-3
Title 7, Composer, Christmas, CX-1
Title 8, Composer, Jazz BAnd, JB-3
Title 9, Composer, Concert Band, CB-2
Title 10, Composer, Pep Band, PB-4

Is this making sense?

This I could do in Access, but again FM is not my dbase of experience.

I'm hoping this is pretty easy? Thank you in advance for any assist or direction where I can find an answer.

 

[JeanW]

If I understand you well, you have the following fields:

Title 1, - SerialNumber, auto enter by system
Composer, - text field - (possible valuelist)
Pep Band, - text field - (possible valuelist)
PB-1, - abreviation + serialnumber = calculation result text

And it is for the last field you want a serial increase...

Make a numberfield fileNr
Make a self join relationship (AbrefSerial) between your BandCode field (Bandcode = Bandcode)

Script the creation of new records, with for the serial something along these lines:

SetField (yourTable::fileNr;Max(AbrefSerial::fileNr)+1

This will lookup for the given Max number and will increase that number with 1, for each match of the relationship.

Your final field needs to be a calculation, result text, where you concatenate the abreviation (PB, JC etc) with the fileNr value, something along these lines:

Bandcode &"-"& fileNr

 

[CryoGen]

Are you using FMP 7.x or higher?

 

[roxannep]

JeanW -

I should have indicated the titles are text fields (they hold the title of the music piece) just as the composer field is text.

So there is no serial number field at this time ....

That's where I need the program to auto-enter based on the rest of my description above.

Again, this is not my program of experience. Where am I making these new fields? In the original database?

In Access, I simply create a new table and set the relationships. Here, I see how to create fields, but it all dumps into one table as far as I can tell, unless I make a separate file ... am I on the right track? Once I know that, the rest of your description makes sense based on how I set these things up in Access.

 

[JeanW]

>>I should have indicated the titles are text fields (they hold the title of the music piece) just as the composer field is text.

>>So there is no serial number field at this time ....
In the table where all the fields are (suppose you have 1 table), just create a numberfield: fileNumber

>>That's where I need the program to auto-enter based on the rest of my description above.

>>Again, this is not my program of experience.
>>Where am I making these new fields? In the original database?
Yes, File -> Define -> Database
You end up in a screen where in the upper left corner you can define tables, fields and relationships.
Choose the table where the fields are and create there the numberfield.

Follow the same way to create the relationship.
But here you have to make a new table occurence of the same table, so that you can make a selfjoin relationship, which is a table related to itself.
Connect the two zqme fields together. Bandcode = Bandcode.

>>In Access, I simply create a new table and set the relationships. Here, I see how to create fields, but it all dumps into one table as far as I can tell, unless I make a separate file ... am I on the right track? Once I know that, the rest of your description makes sense based on how I set these things up in Access.

In Access and FileMaker you can do naerly the same things, only the logic behind the techniques is different.
FM works with tables (entities) and layouts with fields (attributes).

If you're realy stuck and don't mind to send a clone file to
jrraid at yahoo dot com, I can simply put the relationship and the script for you together.
Then you can desect the file to see how it goes...

HTH