Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Scripts to find records in more than one table and display them


Ramzy159
 Share

Recommended Posts

Hi, i am desperately looking for correct scripts and ways on how to display same value of records from 3 tables.

 

I have Resident table, Relation table and Child Table. Each table has age field. Age field in Relation and Child table are in portals

 

 

I want to find age > 60 in those 3 tables and display them in a report.

Link to comment
Share on other sites

I would place all people in same table and then designate the difference between the three categories in one field such as PeopleType that has the choice of Res (Resident), Rel (Relation), or Ch (Child). Then make one People portal with a Global::Age field where People::Age>=Global::Age.

 

 

But in your current configuration make a new table called Age. In that table make a field Age (so it is Age::Age). It can be a global field but does not have to be.

 

In the Relationship tab of the Data definition area make a new field occurrence of all three tables (ResidentAge, RelationAge, ChildAge). For each table create a relationship from Age::Age to each of the Age fields, so you have:

 

Age::Age Resident::Age (Resident::Age >= Age::Age)

Age::Age Relation::Age (Relation::Age >= Age::Age)

Age::Age Child::Age (Child::Age >= Age::Age)

 

 

Then, on a Age table layout, make three portals for each of the three tables (Resident, Relation, Child). Place Age::Age in the header of the layout, and once you enter an age, any value >= Age::Age displays in their respective portal.

 

One last thing: make sure your age field properly displays current age for the current date. There are many ways to calculate age with varying degrees of accuracy. I use : (Get(CurrentDate)-TableName::Birthdate)/365.25

Link to comment
Share on other sites

Hi techpan, Age field in the 3 tables is a calculation field, in which i use custom script to do it.

 

It's YearsInteger (Date_Of_Birth ; Get (Current))

 

The result is in number.

 

My next question is each person in each table will have varies age, so i need to display those who have >= 60 years of age in a report.

 

So do i still have to enter >=60 in the field in the 3 portals that you have suggested???

Link to comment
Share on other sites

 

My next question is each person in each table will have varies age, so i need to display those who have >= 60 years of age in a report.

 

So do i still have to enter >=60 in the field in the 3 portals that you have suggested???

 

If you setup the portals exactly as I suggested you do not need to perform finds, just enter "60" in the Age::Age field. The relationships for the portals do the rest.

 

 

To do this without any changes, go to each native layout and do a simple search, then export the results in Excel. You can then repeat for the two other tables, and then cut/paste Excel results.

Link to comment
Share on other sites

Great it works.. fuh thank you.. please bear with me. techpan, I seriously need your guidance in a couples more hours if you don't mind.. eventhough we are 12 hours ahead of each other.. I really need your help at this moment..

Link to comment
Share on other sites

Ok techphan, this time almost similar case, but the record is now text in drop-down list not number. If I apply the same idea of creating new table called Disable, and later creating 3 portals of the 3 tables as you suggested earlier. How can I display results of all the records that does not contain Normal from those 3 tables??

 

Disablity fields drop down list contains: Normal, Deaf, Blind n Etc. So I want to display all disability from 3 tables in 3 portals except those that are Normal?

 

What will be relationship connection will be?? or other ways??

Link to comment
Share on other sites

Place the word "Normal" in the header text field. The three relatiosnhips this time is "" or "not equal to" which in FileMaker is the equal sign with a slash through the middle. The portals will show anything that is not equal to "Normal."

Link to comment
Share on other sites

It's ok I found it.. i accidently put the field as global
The field in the header in which you enter a search term (for searching for everything but that term) can be a global field, but does not have to be as long as it is native to the layout (just like the Age::Age field). Use the Age table, then a text field "Disability."
Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use