Do you have a Family Table? Seems like a table of Family would be easiest, assuming one Family has many students and one student can belong to one and only one family. If not, probably a calculation that loops thru and omits duplicates before printing labels is your next option
Thank you for your response.
I am fairly new to FM Pro and I am learning as I go. It seems to me it would be easier to create a Family Table (as you suggested) than to create a calculation that loops (I think).
So I created a Family Table with one field "Family Number" and created a relationship to the main table ("Eastern Regional Data"). Then I made a portal in the table to show the following fields from the "Eastern Regional Data" table:
Parent1, Parent 2, Address, City, State and Zip..... but it is not pulling in the info.
Feeling a bit dumb as I feel I have somehow done this in other parts of my database and yet I'm having a hard time figuring this out.
Any help will be greatly appreciated!
So if you base your labels layout on "family table", you'll get one label per family instead of one label per student.
Design Note: I would not use the numbering scheme that you've set up for linking records in relationships. Either a simple serial number with no added format or meaning or a string of text produced by Get ( UUID ) is a far better approach to use.
And you might find that you need a more sophisticated data model if a given student is actually a member of more than one family due to divorce and (possibly) remarriage.
This is a project I have been working on, off a database that has been in use forever before I started working with this company. The numbering scheme, I have no control over because it is based on a system that has been used for 30+ years.
Now excuse my ignorance but I don't understand why when I create a new table "FAMILY TABLE" and create a relationship with the main table "Eastern Regional Data" I cannot see any records in the "FAMILY TABLE".
Here's what I have tried: I insert a field in the "FAMILY TABLE" from the "Eastern Regional Data" table called "Family#" and the field comes up blank
I create a portal in the "FAMILY TABLE" where I select the same field (Family#) from the "Eastern Regional Data" table along with an additional field (Family Street Address) and still it comes up blank.
What I do not see in your description of what you have done is any indication that you have actually created any records in the Family table. Simply defining a relationship does not automatically create records in the related table.
The numbering scheme, I have no control over because it is based on a system that has been used for 30+ years.
You have more control than you think. Just because there is such a numbering system in place doesn't mean that you have to use it in relationships. You can continue to support it as a data field in your records and on your layouts where it can be used to find and sort records, but you use your own internally generated ID for identifying families and linking records in relationships. The users need never even see this id value nor even know that it exists.
Phil thank you for your insight on the numbering scheme. I agree and will definitely make note of it. Hopefully in the near future I will tackle this aspect of the database. To be honest I am a bit afraid of messing up what I have and I certainly lack the experience, knowledge and support to have any confidence in taking this on....for now.
As far as what I have done with the "FAMILY TABLE" ... duh!!! I hadn't entered any records!!! I should have known better than to assume that via the portal the data would be pulled in... So.... I imported all the family numbers. I now can see all the records that are related to the family number. However, because there may be (for example) 4 students in the same family in the "EASTERN REGIONAL DATA" table I get 4 instances of the same address in the "FAMILY TABLE".
Let me just put my end goal out there: I would like to be able to run a search/find in the "EASTERN REGIONAL DATABASE" and be able to view the addresses for only those records in the "FAMILY TABLE" (minus duplicates) in order to generate one mailing label per family.
So now I have my main database "EASTERN REGIONAL DATABASE" which is based on student numbers and a "FAMILY TABLE" which is showing addresses in the "EASTERN REGIONAL DATABASE" related via the "family number". This seems to be in the direction I want to go, but again I would like to see only one instance of the Family number and the related address.
Thank you for your patience and prompt responses, I hope I am providing you with sufficient info. to help.
What you can do is set up field validations that omit your duplicates during the import.
So you can take that Family number field, open field option validations and specify "unique values" and "validate always". Then you can import your records and the duplicates will be automatically omitted during the import.
Excellent! I did exactly what you said and it worked! I have all the family numbers in the FAMILY TABLE minus the duplicates.
Now how do I perform a find/search in the Main database and have only those records show up in the FAMILY TABLE to then print labels from there?
AND Should I create a separate PRINT LABELS Table or just create a Labels layout within the FAMILY TABLE.
Your family table can serve as your labels table as long as you don't want to print more than one label per family. It IS your print labels table.
To find the records you want for your labels, just go to a layout based on Family, enter find mode, specify criteria and perform your find. You can specify search criteria in the Eastern Regional Data table in order to pull up related records in the Family table.
Thank you, but when I specify a search criteria in the Eastern Regional Data table ie. families in "Fall River, MA" I see exactly that, all the families in Fall River, however, when I then go the Family Table I see ALL the families not just families in Fall River...
Never mind I got it!
I changed the settings on the Family Table Layout to show records from the Eastern Regional Database and it worked!!!!
THANK YOU SO MUCH FOR ALL YOUR HELP!!!!
Better take another look at that. This should result in one label per record in Eastern Regional Database and this was what you did not want to take place.
Put this back to a layout based on the new table, but perform you find on this layout, not a layout based on Eastern Reginal Data. When you have layout's based on two different tables (or different occurrences of the same table), records found on one layout will be completely separate from records found on the other. Thus finding records on a layout based on Easter Regional Database will have no effect on what records you see when you change to a layout based on the new Families table.
Your right Phil! I got a little too excited there!
Is there any way I can do a search in the Eastern Regional Database and have the Family Table show only one instance of the Family # in order to generate one label per household?
Perhaps a way to remove duplicate Family #s in the Family Table prior to printing labels?