automatically populating related tables

[lntrullin]

Hi all -- I just switched over from Access to Filemaker, so I'm a total novice -- bear with me! I have two related tables, Staff and Vaccinations. I have about 90 staff, but I only have vaccination information for about 40 of those staff so far.

 

The problem is that I would like perform searches for staff that are missing vaccinations. If they have no vaccinations on file, they don't have a record in the "Vaccinations" table -- unless I manually enter their ID number and leave the vaccination stuff empty. So right now, about 50 staff are being excluded from the vaccination search because they don't have any information in there.

 

Is there a way to automatically populate the "Vaccinations" table such that any time I add a record to the "Staff" table, it creates an entry in the "Vaccinations" table? That is -- it would add their ID, but no other data?

[Cable]

Yes, you can script the creation of as many related records as you want. Just capture the parent ID in a variable, go to the child table and create a new record, set the key field from the variable, and switch back to the parent layout.

 

But, I don't see how that is a good solution to your query. If I was doing it I would search for staff that have vaccinations and then show omitted to see the staff that don't.

[LaRetta]

As Cable points out, adding needless empty records is never a good solution.  From Staff, you can search for * in the Vaccinations::ID field.  This will produce a found set of Staff who have vaccinations.  Then performing a Show Omitted will give you those Staff without.  :-)

[lntrullin]

Ok, that makes a lot of sense. I'm embarrassed to ask, but how does do a search as LaRetta described? Do I perform a find from within Staff? The two tables are linked by the employee ID.

 

If I do a search for * in the Staff table, it pulls up everyone (vaccinated and not) -- I'm not sure how to refine the search.

 

Thank you so much for the responses!

[LaRetta]

In FileMaker, perspective is everything.  So while you are standing on a layout based upon Staff, and since you have a valid relationship to Vaccinations, you search the vaccinations table directly.  So it might look like this pseudo-script:

Enter Find Mode [ uncheck pause ]
Set Field [ Vaccinations::ID ; "*" ]
Set Error Capture [ on ]
Perform Find [ ]
If [ not Get ( FoundCount ) ]
Show Custom Dialog [ OK ' "No staff member has vaccinations so all staff will be returned" ]
Show All Records
Else
# some staff has vaccinations so only show those who don't
Show Omitted Only
End If

Note that you do not need fields from Vaccinations (and don't need that vaccination ID field) on your staff layout when you run this script.

The vaccination ID is used as an example - it can be ANY field which is guaranteed never to be empty.  Every table should have a unique auto-enter unique ID so that is always safest to use for things such as this.  :-)

[David Nelson]

Does this work with all tables?  I have been going to the other table, searching, showing omitted records then using Go To the Related to go back to the original table with the result of having those parents with children which failed the search.  I am struggling to picture this.  Would the result be the same and is there any time this would not work?  which would be fastest?  Thank you for considering my post.

[LaRetta]

For one, your method will not find parents without any children.  You can test the two methods side by side with your data and observe the results and let us know!  Otherwise I'll try to respond more in depth later today with some comparisons.  :-) 

[David Nelson]

Hello LaRetta,

 

I see what you mean about not always getting all the records I expect using my method.  When in child table, a GTRR back to parent will not include parents without children because there would be no related parent to return to.  It seems best that if I want a result record set of parents even if based upon child data that I stay in parent.  Is it safe to say this works same way if searching grandchild table?  I suppose I could test but I am not always sure if my results hold true in all examples so I thought I would ask just in case.

[LaRetta]

I cannot give you a definitive answer, David.  I assume if the relationship is sound then searching no matter the depth of level, the results would be the same.  I would look at it this way:  If you can view data in a grandchild portal on a parent layout, you can also safely search that grandchild table and be safe (directly through the relationship even if portal were not there).

 

However, there is a peculiarity called the 'grandchild issue' that might throw a curve ball so I cannot say for sure.  Maybe others know the answer or I will try to do some testing when I get the chance.  I am also unsure if performance might degrade, compared to going to grandchild table.  Since the relationship would be sound and if the fields being searched are indexable, I should think it works as well as searching local table.  But I am not positive about that.

 

Great question!