fmp17 Self-join Results with multiple databases

[Jhon Doe]

Hi, I hope you having a wonderful day,

Currently I'm traying to create a dashboard with Filemaker, the idea is show several portals and grafics of differnets databases but with specific criterias. The case that i'm trying to resolve is the next one:

There are three database related, one is for clients, another is the contract of this clients (services) and the last one is client's tickets, so we have the next relationships:

tickets DB ------ Clients ------ Services  

(I didn't create that DB distribution). The idea is show in the dashboard which tickets were created for clients who have certain services. The first thing i did was create a self join relationship of  Services to filter those clients who have the services i want to filter, next, i put this relationship into a portal in a layout based on the Services database and it works, but if i put into the portal a field related with the client's name, the portal doesn't match the results of the self join relationship whit the correct client, neither with the tickets that it supposed are related with the results of the self join relationships, how can I created a query in Filemaker taking into account more that one database? I made the  self-join in the manage/database/relationships menu.

[mr_vodka]

databases dont really matter.  You deal with table occurrences and then can be from the same table or a different database file.

Post a copy of your file.

[Jhon Doe]

13 minutes ago, mr_vodka said:

databases dont really matter.  You deal with table occurrences and then can be from the same table or a different database file.

Post a copy of your file.

Thanks for your answer.

Saddly I cannot share the file with you, the fact is that i want to show in a portal tickets related to clients that have specific services. I create a self join on Services to filter the clients who have some services but I cannot connect these results with the tickets database, how can I do that?

The idea is put a lot of portal with different criteas, so i discarded the idea of create a script.

[bcooney]

I’d build a dashboard  for this by finding all service records that match the service or services you’re interested in. Once found, I’d return the client ids from those  service records and use that client idlist in a selector (global text field). The relationship would be clientid list to tickets by client id. 

[bcooney]

I question the data model. If clients can have many services, and services can be associated with many clients, you need a join table to capture  each ServiceClient instance. If Services is the join table, do you have a service type table? What keys are in the services table? I would expect servicetypeid and clientid. 

[Jhon Doe]

On 5/25/2019 at 7:04 AM, bcooney said:

I question the data model. If clients can have many services, and services can be associated with many clients, you need a join table to capture  each ServiceClient instance. If Services is the join table, do you have a service type table? What keys are in the services table? I would expect servicetypeid and clientid. 

Yes, that's Right, but there is a difference, instead of services table, is a contract table where the services are describe, so each client could have many contracts but one contract is only related to one client. Each contract has an ID and boolean fields for services acquires.  This is the part I could filter, identify the clients who have certain services.

[bcooney]

You said in the original post that you have a services table. Regardless, if you have fields in a contract table, ‘HasService1”, I would still change that to a table of services by contract Id.

[Jhon Doe]

On 5/28/2019 at 9:30 PM, bcooney said:

You said in the original post that you have a services table. Regardless, if you have fields in a contract table, ‘HasService1”, I would still change that to a table of services by contract Id.

Yes, sorry about it, i realized that after made this post, i'm going to check your idea, thank you for your help.