auto number generation in FM10

[saralee]

Hi everyone. I need to generate some auto numbers as the primary key IDs for some tables in my database. How can I do so in Filmaker10? For example, I defined a field called BabyID and set it as "serial number" and "on creation", but nothing happened.

Please advice. Thank you! ???

[bruceR]

There is no reason that shouldn't work. So it will be better if we can see your file, or a simplified example.

[saralee]

oh maybe I missed mentioning an importing point - I already have a database with data populated by another person, and I'm revising the design trying to make it work better.

It is a database recording all the data of babies being researched on by a research centre. Originally, he had one big table with everything inside (babies info, parent info and studies info). I created three separate tables (Babies, Households and Studies) and tried to export the data from the original database to these three files, but I need to link these three files in the right way. So in the original big table, I created three new fields (Baby ID, Household ID and Studies ID). I wanted to populate these IDs somehow before exporting them into the three new files, but I couldn't ask FM to automatically generate these IDs for me. I guess it was because I already have the data in the tables/fields. Does it make sense? I'm generally stuck by the issue of combining three relationship tables by IDs..

Thank you so much! ???

[comment]

To populate the field in existing records:

Show all records. Click in the BabyID field and select Records > Replace Field Contents… > Replace with serial numbers (with Update…).

[saralee]

Thank you, comment!

Sorry about me being slow - I couldn't find the Replace option anywhere.. When I click on the BabyID field, only the field/control setup dialog box popped up. I can replace the field content there..

Thanks..

[comment]

Do this while in Browse mode, not Layout mode.

[saralee]

This is great! I got it work

Thank you so much for your patient teaching, comment!

I will come back tomorrow and might have more questions to ask~

[saralee]

Hi comment

I'm back for more questions! So for the big giant table I have now, I want to separate them into tblBabies, tblHouseholds and tblStudies. Now the big issue is how I can assign the different IDs to the three tables. Should I do it in the original big table or should I do it in the three tables? I guess I should do it the three tables, but then how to link the tables with IDs to represent their linking relationships in between?

For example, now I have tblBabies, tblHouseholds and tblStudies with all the related data exported from the original table, but there is no linkage in between these tables. I only have a relationship among the tables designed. Hope this makes sense.

Please advice. Thanks a lot!

[comment]

I don't recall a studies table. What are the relationships here? One household to many Babies, obviously - but how are the studies supposed to be related?

[saralee]

Oh, sorry I forgot to mention the tblStudies.

Basically we run multiple studies on each baby and we record each study's visit time, study name, lab name and such in the database. So each study record shall have a separate ID and one baby is linked to one or multiple records.

Right now I'm thinking of writing scripts to determine which babies are matched with corresponding parents via the same phone numbers. Does it sound like I'm on the right track?

Thank you!

[comment]

Ah, so studies are what you called visits earlier, the ones listed in repeating fields?

OK, one at a time:

Babies -< Studies

Hopefully, you have imported the BabyID field along with the repeating fields into the Studies table. So now you only have to link the two tables by BabyID, and place a portal to Studies on a layout of Babies.

If you intend to add more studies, allow the relationship to automatically create records on the Studies side.

Households -< Babies

This one is going to be more difficult. But first you need to clarify what field/s identify a household - IOW, when you compare two baby records, how can you tell they are siblings? Is it purely by phone number? And are these phone numbers formatted consistently?

[saralee]

Thank you, comment! That helps!

Yes, the visit time and stuff are for this tblStudies.

So I tried to place a portal to Studies in the Babies layout, but nothing shows up.. Do I need t sort the fields or something? I also couldn't find the option to turn on the function to automatically create the records...

Yes, all the phone numbers are in the consistent format. I guess the determining factors for siblings are same phone numbers and/or email addresses (sometimes the family has one out of the two).

Thanks!

[comment]

So I tried to place a portal to Studies in the Babies layout, but nothing shows up.

Then something is wrong - because if you imported the BabyID into Studies, you should have a match.

I also couldn't find the option to turn on the function to automatically create the records...

It's in the relationship's definition: go to the relationships graph and double-click the = sign on the connecting line.

I guess the determining factors for siblings are same phone numbers and/or email addresses

This is a crucial point, because in the next stage you will import ONE of the siblings into the Households table. If one of rwo siblings has a different phone number/e-mail, they will be in two different (duplicate) households.

In addition, if one of the siblings has additional data, that data may be lost in the process. For example, if father's first name is "Joe" in one record and "Joseph C." in another, you may end up with only "Joe" in the household record. If you're not sure about this, then there is an extra stage of data cleanup.

[saralee]

Thanks!

Now my portal in Babies layout is showing up content

But I still need to figure out how to line up my fields properly.. I would like to have my fields (date of visit, lab name, study name...) show up on the left side (the first column) and display each study's info to the right... something like this-

Date of visit: (record1) (record2) ...

Visit time:

Age on Expr Day:

Study name:

Study lab:

Degree:

Results sent:

But I only found row setting in the portal setup box.. Could you enlighten me?

Regarding the parents/babies matching, I have actually removed all duplicate parent records, and am now ready to match them by phone numbers and email addresses.. will see how it goes

I removed the duplicate parents before

[comment]

I would like to have my fields (date of visit, lab name, study name...) show up on the left side (the first column) and display each study's info to the right

That's not something that can be done easily (if I understand your question correctly). Filemaker can show any number of rows in a portal (with scrolling, if necessary) - but the number of columns is fixed. So it would be best to place your labels above the portal, rather than to the left.

[saralee]

Thanks, comment! I'm doing it in the row setting now.

I thought I got the matching part of babies and studies, but aparently I didn't

In my tblStudies, I have a field called BabyID which is supposed to link studies with the targetted babies, but by simply exporting the BabyID from the previous big giant table, I couldn't get the BabyID in tblStudies at all.. I guess the issue is there is no relationships indicated in the previous big giant table to begin with..

Do I need to create scripts to match babies with their studies then? This time the determining factors would be the babies' full name... I just need to copy the fields babyLastName and babyFirstName in tblStudies and compare that with the same fields in tblBabies? At least that's what I'm doing for babies/parents matching. Still trying to figure out the scripts though..

Thanks!

[comment]

I am starting to lose the focus here, so let me restate what I know so far:

1. In the "giant" Babies table, you have (among others) these fields:

• BabyID (Number , Auto-enter serial)

and seven repeating fields for the studies:

• Date of visit

• Visit time

• Age on Expr Day

• Study name

• Study lab

• Degree

• Results sent

2. In the Studies table, you have the same eight fields (none of them are repeating, and no auto-enter options are checked), with the same data types.

3. You have imported the records from Babies to Studies while splitting the repeating values into individual records (so that a Bay record with 3 repeating values would produce 3 records in the Studies table).

4. You have defined the following relationship:

Babies::BabyID = Studies::BabyID

and placed a portal to Studies on a layout of Babies.

Please check back and confirm (or redo if necessary). Unless something went wrong, the records with 3 repeating values should now show 3 related records in the portal.

[saralee]

Hi comment!

You are right about all the updates and I was able to make the portal work in the tblBabies:) Thanks again for your patient help!

Right now I need to link tblHouseholds with tblBabies. The way I'm doing is to copy the phone number and address from the original big giant table in the tblBabies and run scripts to compare if they are the same as that in any tblHouseholds record. If they are, then I assign that specific Household ID to the HouseholdID in tblBabies.

Does it make sense? I could not get my script to work though. I'm just not familar with the different scripts. Should I go to the two layouts (Babies one and Households one) and loop from there? And how can I place any value in a specific field? Do I do "set field" or...?

Thanks a lot!! :)

[comment]

I believe I would take a different approach here. This is assuming a household is uniquely identified by either phone OR e-mail.

1. Define a new calculation field in the Babies table cUniqHH (result is Text) =

Phone & ¶ & eMail

2. Create a new Households table with the following fields:

• HouseholdID (auto-entered serial number)

...

(all the fields in the Babies table that pertain to household)

...

• UniqHH (Text)

Set the UniqHH field's validation to Unique, Validate Always.

3. Go to the Babies layout and show all records. Go to the Households layout and import records from Babies, mapping cUniqHH -> UniqHH (and of course, all the household fields in Babies into the corresponding fields in Households). Perform auto-enter options during the import.

4. Define a relationship between the two tables:

Households::UniqHH = Babies::cUniqHH

At this point you should pause and examine the results. Each Baby should have exactly one related record in Households, and each Household should have at least one related Baby. I would also sort the Household records by various fields and eyeball them for obvious duplicates.

5. Define a new HouseholdID field in the Babies table. Put in on the layout, show all records, click in the field and replace the contents with a calculated result = Households::HouseholdID.

6. Change the relationship between the tables to:

Households::HouseholdID = Babies::HouseholdID

---

P.S. Don't forget Step 0: have a backup!

Edited June 1, 2010 by Guest

[saralee]

Thank you so much, comment! I was able to migrate all the parent/household information to the new tblHousehold. FM removed duplicate records for me automatically (is it normal?) and now I'm getting some help to fix some other hidden duplicate household info before matching the babies with their parents.

The tricky part is that each household is not strickly identified by phone number or email. There are many missing and inconsistent data, so I ended up defining the UniqHH field as-

Mom's firstName & Mom's lastName & Address & City & Email & PhoneNumber

thanks!

[comment]

FM removed duplicate records for me automatically (is it normal?)

That was the idea behind validating the field as unique.

The tricky part is that each household is not strickly identified by phone number or email

Well, now you know why I said:

This is a crucial point

???

[dude75]

Im so glad im not the only one having trouble with this! I have been trying to incorporate serial number labels in to table fields for various different projects but have come across similar problems to this. After reading through your comments I have managed to get it sorted out.

Many Thanks

[saralee]

glad the the discussion helped out I might come back with more questions

Good luck with your project, dude75.