AnsweredAssumed Answered

Searching multiple fields over multiple tables.

Question asked by arcamenel on Oct 20, 2017
Latest reply on Oct 20, 2017 by philmodjunk

Good Day,


I am currently working with the trial version of FMP16 trying to ascertain whether it will be able to do what I need before purchasing it.


I am fairly certain this should be possible but I am completely inexperienced with the platform and after much google searching it seems a lot of the information is for much older versions and I could use a pointer in the right direction for this specific scenario.


My issue is with carrying out a search that can return info from multiple tables when the information will not always be in a matching spot.


I have 3 Tables: Clients, Corporations and Foundations.


In the client table I hold information like their name, ID #, phone #, etc.

The Corporations and Foundations tables are similar in the type of data they hold but they have different structures, these will generally hold the entities name, who the owner is and beneficial owners as the main fields that are relevant for this question. (it will hold additional data like date it was registered and similar data but that is not what I need to be searchable in this case)


My issue is an example a corporation can have 3 directors and it can also have 3 officers who are not necessarily the same person holding 2 positions it can also be the case where none of those are the owner. It can also have quite a few shareholders depending on how they divide them.


I need to be able to search by name and have it look at my client table for a matching client name record as well as show me any results for corporations or foundations in which they may not only be the client of record but could be a dignitary or a shareholder. If the searched name is present in multiple entities I would need all of them to be displayed.


The issue I’ve found with the basic search is there is no way to standardize these records for me as someone who is a client can be a shareholder or director in a corporation in which someone else is the client of record, and I sometimes will need to be able to determine if someone is a shareholder and there are 10 possible relevant shareholders I need to track so knowing which slot they are in is impossible leading to me having to run the search 10 times just to figure out if someone is a shareholder let alone hold another position.


Thank you for taking the time to read this I know it is overly complicated and long but I wanted to cover as many questions as possible in one go.


TL;DR: Need a search that will cover multiple tables and fields for records that cannot be entirely standardized as far as what field a particular piece of information will be.