Help with Formula Variable
I asked a similar question a while ago, but want to get in depth more. If anyone has any ideas on how to make this happen, or where I could learn more about how this works, I'd appreciate it. I'm...
Forum Discussion
Yes, this can be done. You will need these tables and relationships
1 Company has many contacts
1 Company has many Material Category Company Assignments.
1 Material Category has many Material Category Company Assignments.
1 Material Category has Many Focus Material categories.
That last one is the "trick". There will be just 1 record in that table called Focus Material Category. It will be Record ID# 1. Once you create it, lock down in permissions that no one can add or delete.
So, for example select Steel as the Focus Material Category.
On the last relationship, make a summary field of the [# of Focus Material Categories]. Then make a formula checkbox called [Material Category is in Focus]. [# of Focus Material Categories] >1
Look that up down to Material Category Company Assignments.
Then make a summary in companies of the [# of Material Category Company Assignments] where [Material Category is in Focus]
Make a formula checkbox field called [Company has Focus Material Category] [# of Material Category Company Assignments]>0
Look that up into Contacts.
Now, on the Contacts table make a formula field with a formula of 1 called [Report Link connection to Focus Material category (=1)].
Almost last, make a report link field type on the table
Focus Material Category to connect to contacts based on the Record ID# on the left and the [Report Link connection to Focus Material category (=1)].
Set he form Properties to show the contacts on the record.
Now you should be seeing all contacts for companies who sell steel ion that record. :)
1 Company has many contacts
1 Company has many Material Category Company Assignments.
1 Material Category has many Material Category Company Assignments.
1 Material Category has Many Focus Material categories.
That last one is the "trick". There will be just 1 record in that table called Focus Material Category. It will be Record ID# 1. Once you create it, lock down in permissions that no one can add or delete.
So, for example select Steel as the Focus Material Category.
On the last relationship, make a summary field of the [# of Focus Material Categories]. Then make a formula checkbox called [Material Category is in Focus]. [# of Focus Material Categories] >1
Look that up down to Material Category Company Assignments.
Then make a summary in companies of the [# of Material Category Company Assignments] where [Material Category is in Focus]
Make a formula checkbox field called [Company has Focus Material Category] [# of Material Category Company Assignments]>0
Look that up into Contacts.
Now, on the Contacts table make a formula field with a formula of 1 called [Report Link connection to Focus Material category (=1)].
Almost last, make a report link field type on the table
Focus Material Category to connect to contacts based on the Record ID# on the left and the [Report Link connection to Focus Material category (=1)].
Set he form Properties to show the contacts on the record.
Now you should be seeing all contacts for companies who sell steel ion that record. :)