How to create a report link filtering a table via multiple fields?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered
I have a client table with five fields to which associates can be assigned, so each client record has five roles ("Role 1", "Role 2" etc), each with one name in it (though an associate can fill more than one role per client).

Is there a way to create a link from the associates table that would display a list of client records in which associate appears in any of the five "roles" fields? If so, how would that be set up?

Thank you!!
Photo of Amber

Amber

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 640 Points 500 badge 2x thumb
I have not tested this but it should work.

Create a formula field on the details (Target) table which will calculate to TRUE if the current user is in any of the 5 Role slots.

[Role 1] = User()
OR
[Role 2] = User()
OR
[Role 3] = User()
OR
[Role 4] = User()
OR
[Role 5] = User()

Then on the source table make a  formula checkbox field which will always be TRUE with the formula TRUE.

Then make the Report Link field based in those two field being equal.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Hi Mark,

This helps with my question as well but can we go a little further? In the example above the "associate" in each role field is not a user but is a lookup field from another table. So how would you construct that formula?

What I'm essentially looking for is when a "contact name" is listed in either [Contact 1] OR [Contact 2] in the Activities table then show that contact's all related activities in a report embedded on the Contact table.

This answer came so close, I'm hopeful there is a way to accomplish this! Thanks so much!
You have not really described your relationships.

If you have a Relationship where 1 contact has many Activities then you could just include the Report a Link field on you contacts Record, and then you would also have a second relationship for Contact 2 and you could do the same.

But, I'm guessing from your question that you have not used relationships.

But you can just use 2 Report Link fields.

So just create a report link field on the contact table. When you configure it, on the left side choose the field in the contacts table for contact name on the contacts record. On the right side navigate to the Activities table and select contact 1.

Then put that Report link field on you Contact form and choose in form properties to display the activities directly on the form. I suggest making a report with no filters on the Activities table to use on the form for the report link field so you can choose the columns to show.

Then copy that field again and configure it to link up to Contact 2.

You will end up with two reports of Activities, the first being where the Contact is Contact 1 and the second where they are Contact 2.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Hi Mark,

Thanks for responding so quickly. I have relationships between the Contacts and Activities tables which allows for easy implementation of your suggestion. My question is more specifically, is there a way I can combine these results so that I have a single report that says Contact Name is included on all of these Activities (in either primary or secondary role - because in the contacts table the description doesn't matter it just matters all activities each contact is associated with.)

The issue I'm running into with the report links is that to link my Contact in the Contact table I need to pick either Primary OR Secondary Contact in the Activities table as my targeted link. If I can create one field in the Activities table to link back to the name in the Contact table (similar in a way to your previous solution) I maybe able to get the combined report I'm looking for.

Thanks again!!
I don't think that my original post would have worked, but this was bugging me that there must be a way.

How about this for a solution.  Make it a many to many relationship.  One contact has many Activity-Contacts and one Activity has many Activity-Contacts.

Then the report link field is easy and of course you pull down on lookup field anything you need from the Activity record.

You would need to migrate your data to this new middle table, but it should be totally safe to coax that data into the new table since you do not need to get rid of the existing relationships and fields for contact 1 and contact 2 until you get the others working.

I'm not sure what the key fields are for your two tables, perhaps just the record id of each table (contacts and Activities).  So it must be easy to export a spreadsheet or table to table copy to compete the two fields you need on that middle table -Related Activity and Related Contact.  It should be just two imports to load it up. One for the relationship to contact 1 and then again for the relationship to Contact 2.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Mark, thank you for the continued support! We are getting there! Your assistance is beyond appreciated.

So I created the intermediate table, and just so I understand you, from this table form is where my users will enter the Activity information (instead of on the previous activity form) and pick the related contact information?

Here is the issue I'm still facing though: the reference between the Contact table and the new Contact Activities table still can only reference one field (either the primary contact or secondary), so I'm left with the same conundrum. How do I relate the Contact to an entry in either primary or secondary contact field in the related activity.
The users will still enter the Activity data on the activity record.  Then they would save the record.  Then either using the Add Activity-Contact button or in grid edit they would add some Activity contacts.  (sorry for being slow - I'm up at the cottage now until Friday ....).  So if there were two contacts they would "Add Activity-Contact" twice.
I just realized that you can add the Activity record and allow in Form Properties for the embedded Report Link report editable, you can add the contacts at the same time you are saving the Activity record.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Mark you have saved the day! Thank you for all the detailed support. I've got the many to many relationship working and plan to implement similar relationships within the application. This has been a huge help!
Yey!  Thx for jetting me know. I think I will be able to use this technique myself someday, so it's handy to have a new trick in our bag of QuickBase tricks.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Mark, is it ok to ask you a further question about this many-to-many relationship we were discussing?

At this point everything is working but I have another table that I would like to pull the same combined information into and I cannot figure out how/where to set up the relationships to get the same report into the other table, here's the set up:

Client Contacts = Client Contact Opportunities = Opportunities (where client contact opportunities is the connector table)

Now I need another table (IO Table) to pull the same report coming from the Client Contact Opportunities table, showing up on the Opportunities form, that shows all client contacts associated with the opportunity. Opportunities are already related to IOs in a one to many relationship.

Where would I set the link to get that report to pull related contacts based on the opportunity that is related to the IO record?

Thanks again for any help!
Can you explain which is the one and which is the many in the relationship between IO and Opportunities?
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Sure Opportunity should be the parent table (it's really a one to one relationship but Opportunities is the parent)
OK, so create a report Link field.  Most people assume that you can only have a report link field if you have a Relationship between the tables, but that is not true.  Then configure the two sides of the Report Link field 9when you crate it, then you edit the field properties.  
On the IO table you will have the field [Related Opportunity].
The field on the right hand side will be to search for the the app itself, and then the field on the table of that middle table of Client Contact Opportunities for the field [related opportunity].

Then, once you get the right record, you make a little report on the middle table with no filters but showing the contact name field.  Then tell the form property for that Report Link field to use that report so that you see the contact name..
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Worked like a charm! You rock!!! :)
:)