7 Replies Latest reply on Jul 24, 2014 3:49 AM by Annette

    Layout Based on what table/relationship



      Layout Based on what table/relationship


           Please help!  I'm going crazy trying to figure out what I'm doing wrong. 

           I have the following tables:

           ClientInfo:: CIDPK, Name, Address, Etc

           Referrals::RIDPK, CIDFK, ReferralDate, Referral Source, Etc.

           Service::ServicePK, RIDFK, ServiceDate, ReviewDate, Etc.

           Clients_Reports::CRIDPK, CIDFK, RIDFK, ServiceFK

           Relationships as Follows:

           ClientInfo::CIDPK = Referrals::CIDFK

           Referrals::RIDPK = Service::RIDFK

           Client_Reports = each of the PK in the three other tables.


           Each client can have multiple Referrals and each referral can have multiple services.

           On a layout based on the Service table (which shows fields about the client and the Referral the service is a part of) I have an image that I want the user to click and using a script it will open another layout (which I had based on Client_Reports).  It has merge fields showing the clients name, address, referral date, referral source and the service info.  The script at the moment sets variable for each of the PK's, opens the layout based on the related record, creates a new record and sets the appropriate fields to the variable created.  This does not bring up the correct referral/service info onto the layout.  If I tried changing table the layout was based on I get that they are not part of a related table. 

           So I'm not sure, are my relationships wrong, is the layout based on wrong table or is the script wrong?  Or all of it?  Am I making this more complicated than it needs to be?


           Please help, ready to pull my hair out.

        • 1. Re: Layout Based on what table/relationship

               Can you screenshot a picture of your relationships? And also the layout you are trying to set up? I'm having a hard time picturing what you're trying to accomplish

          • 2. Re: Layout Based on what table/relationship

                 It could have something to do with your script as well...depending on what you assigned it to do!

            • 3. Re: Layout Based on what table/relationship

                   This looks like a table occurrence problem.

                   Your first set of relationships look like this from what I understand:


                   But then you indicate that "Clients_Reports = each of the PK in the three other tables."

                   That would seen to indicate direct relationships to all three of the other tables, but this would produce "loops" in your relationship graph and FileMaker will insist that you add additional "instances" (occurrences) of some of your tables to eliminate what would otherwise be an ambiguous relationship. Since FileMaker might try to make additional occurrences of Clients_Reports or it might make additional occurrences of the ClientInfo and Referrals tables, you'll need to tell us what you have so that we can help you sort this out.

                   For more about table occurrences (instances), see: Tutorial: What are Table Occurrences?

              • 4. Re: Layout Based on what table/relationship

                     Hey guys, sorry that I don't explain it clear enough to make sense.  I'm never sure how much info to throw at people when asking for help.  You are correct Phil, it created an additional table occurrence for the tables i have related to Client_Reports.  I will attach screen shots as requested. 

                • 5. Re: Layout Based on what table/relationship
                  /files/8bc068afee/Layout.png 885x558
                  • 6. Re: Layout Based on what table/relationship

                         After the script copies over the IDs for Client Info, Services and Referrals,

                         Add fields to your layout from Client Info 2 to show data from the client info table.

                         Add fields to your layout from ClientReports_EOC to show referrals data

                         Add fields to your layout from ClientReprorts_ServiceActivity to show Service data.

                         In each case, when you get the specify fields dialog or field picker dialog up, you first select the table occurrence by name from the drop down and then select the field that you want to add.

                         If that doesn't work for any one of these three table occurrences, you'll need to check your script to see why the correct value isn't being set to the match field in Client_Reports 2 that matches to a record in that table. You may need to post your script if you can't spot the error.

                         To post a script to the forum:

                    1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
                    3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
                    5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                    7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                    • 7. Re: Layout Based on what table/relationship

                           Thank you Phil, I was referencing the wrong tables as you said.  Such stupid mistakes......smh.