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

Calculation involving related records in join table


prf27
 Share

Recommended Posts

Hello,

 

I have a question regarding calculations involving related records. I'm a relative novice at FileMaker design, so please forgive any obvious mistakes. However, what I'm trying to accomplish would be trivial in some other framework that let me query the database directly, so I'm pretty confident it can be done.

 

The tables in question are Person, Program, Person_Program, and Requirement. (Person_Program is a join table that relates Person and Program; it's essentially a person's participation in that program.) A program can have many requirements. I want to have a calculation field somewhere that compares

 

Person -- Program -

 

I have a Person_Program layout with a portal that lists all the Requirements for the related Program. Basically, all I want is to run a calculation in each portal row that compares a field in that row to another field in the related Person record (I'm checking if the person has fulfilled the requirement). When I try to do this the obvious way and perform the calculation in the Requirement table, it will only run the calculation for the first related Person record.

 

From my experimentation, I don't see how this can be done without linking each of a Program's Requirements directly to the Program_Person instance. But this is essentially unmaintainable since requirements can be modified. It seems like I should just be able to run the calculation in-line in the portal row.

 

It's very frustrating seeing the two fields I want to compare sitting right next to each other, but not being able to run a calculation between them.

 

Thanks in advance.

Link to comment
Share on other sites

I think you can solve this by using a navigation script when going to this layout, then use the script step "Go to Portal Row" (if you have more than one portal then name the portals and use "Go to Object [ObjectName]." Next you can set a field by storing sequential field value in variables, doing your comparison and then using "Set Field" with the comparison.

Link to comment
Share on other sites

I think you can solve this by using a navigation script when going to this layout, then use the script step "Go to Portal Row" (if you have more than one portal then name the portals and use "Go to Object [ObjectName]." Next you can set a field by storing sequential field value in variables, doing your comparison and then using "Set Field" with the comparison.

 

Perfect! Exactly the (simple!) solution I was looking for. Thanks a ton.

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use