10 Replies Latest reply on Nov 29, 2012 4:37 PM by philmodjunk

    Table Relationships



      Table Relationships


           I have what I think is a pretty basic question, but not being overly skilled at FM or Database constrution in general I was hoping someone here could help me out:

           I have created a master table with all customer details (name, address, company, etc.) and have linked it successfully to past purchasing history.  This creates a large database with a lot of information.  Next, each customer is assigned a salesman and I have created a new table for each salesman. 

           I am looking to populate the salesman tables with information from the master database with information limited to only those customers assigned to the specific salesman.  The problem is I can't seem to link and populate the salesman table from the master table.  I am looking for the salesman to log in to the table and see all of his customers (I don't want to use a portal).

           I guess I am looking to mirror the main database except filtered for the individual salesman.

           Any ideas?

        • 1. Re: Table Relationships

               You will have to have a salesman field in the main table.  You wouldn't have to have this link to the main table.  I would create a drop list field on the customer detail screen with a list of salesmen and you would assign a client by selecting the salesman from the drop down list.   You would then have a script that runs based on the login user name that performs a find of that salesman.

          • 2. Re: Table Relationships

                 Thanks for the answer.  It sounds however like I would need to use only one table, and filter it based on who logs in.  The problem with this that it is a large database (when fully implemented can have over 1 million records).

                 Thinking about my problem, perhaps the better way to ask my question is:

                 How can I populate a second database with all of the record information from the first database but only have records based on a field match (salesman)?


            • 3. Re: Table Relationships

                        (I don't want to use a portal)

                   Sorry, but that's like saying "I want to row this boat but don't want to use two oars". wink

                   Portals are a very necessary tool for working with groups of related records from the context of a specific record in a related table. A portal to your "master table" from a layout based on a table of SalesPersonnel would definitely be one way that your Sales Personnel can work with their customer records.

                   Whether you use the portal or not, you can link sales personnel to customer records if you add a table of SalesPersonnel and link it by an ID number defined in SalesPersonnel you your existing table of customers.

                   If necessary, you can also use record level access controls to limit the records they see to only those assigned to them. If that's of interest, wee "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

              • 4. Re: Table Relationships

                     Thanks Phil, I'll check that out.

                     The problem with the portal that I see is that I won't be able to use all of the search tools available inside the portal (though I really don't know if that is true or not). 

                     When I started this project I began with the Contact Management starter solution and modified it.  So my thought was that the salesman's database would be a clone of the master database (and all its functions) but filtered only for their use. 

                     Filtering the master database for each salesman using record level access control you describe sounds like it might be a possible solution, I'm just worried about the time it takes to execute a search.  Will it have to search the 1 million records, or just the 40,000 the salesman has control over?


                • 5. Re: Table Relationships

                       Much depends on design details that I can't see from here. Searching a million record table (I have a few of those), can be reasonably quick if your criteria is based on indexed fields local to the table on which your layout where you perform the find is based. Include criteria in an unstored field, and you can see everything slow to a crawl. Refresh Window [flusch cached join results] is also a script step to avoid at all costs when working with such a table.

                       I would advise against splitting up your data into different tables of identical structure, one for each salesman unless there is no alternative as this can really complicate a lot of different parts of your solution.

                       One trick that works quite well when you need to specify search criteria in an unindexed field of such a table, is to perform two finds. Make the first one using only criteria in indexed fields, then return to find mode, specify criteria in unindexed, or fields from related tables, but use constrain found set instead of perform find to reduce the found set produced in the first step to just those records that match criteria specified in both finds.

                       Portals are an option, but not the only option when working with related data. You can certainly perform a find on a table based on the records you would otherwise show in the portal and display the search results in a list or table view that includes fields from the parent table.

                  • 6. Re: Table Relationships

                         OK, I have created a custom privilege set and that seems to work reasonably well.  One question I have is: When clicking on the next record button in the upper left corner, I come across records that the salesman is not supposed to see.  All the fields say <no access>.  Can I hide those records automatically and have the total records (unsorted) display only the number of records available for that salesman?




                    • 7. Re: Table Relationships

                           Nobody seems to have noticed this in your original post. Do NOT create a new table for each salesperson. Instead have a table of salespersons.

                      • 8. Re: Table Relationships

                             If you perform a find, any "no access" records that match your search criteria are automatically excluded from the found set produced by performing the find.

                             Thus, a script similar to this will eliminate the "no access" records from the user's found set:

                             Enter Find Mode []
                             Set Field [YourTable::SomeFieldThatIsNeverEmpty ; "*"]
                             Set Error Capture [on]
                             Perform Find[]

                             This script finds all records that the current user is permitted to access. If you have FileMaker Advanced, you can install a custom menu that performs this script when "Show All Records" is selected from the Records menu.

                        • 9. Re: Table Relationships

                               Thanks Phil.  So far, so good.

                                I don't have FileMaker Advanced but will probably be purchasing Server Advanced soon.  I can implement the script after that...



                          • 10. Re: Table Relationships

                                 Server Advanced does not give you the ability to do this, FileMaker Pro Advanced does.