4 Replies Latest reply on Sep 2, 2009 12:19 PM by philmodjunk

    Showing a client's other jobs in a portal.



      Showing a client's other jobs in a portal.


      The database I'm working on has two tables of relevance to my problem. They are called 'Job's and 'Clients'. All job records have a unique number and so do the client records. The two tables are linked together with a relationship using these unique numbers.


      The 'Job' table is the primary layout - showing all the details of that particular job and also the linked client info from the 'Clients' table. So far so good.


      What I'm struggling to get working is:


      I'd like to put a portal window on the job layout which would automatically show a list of all the other jobs we have in the database for that client. This would allow users to see other work we've done for this client - which would be a big help to keep track of a client's pervious job history without the user having to run specific searches.


      At the moment all that shows in the portal field is the same record I'm currently on which is pointless. I know I need to say to the system 'take this client number and find all the jobs we have for them and then display the job number and title fields in the portal window'. Does anyone have any suggestions what calculation instruction I'd need and any other pointers to get this to work?


      Thanks for any help you can give 



        • 1. Re: Showing a client's other jobs in a portal.

          Create a new relationship:


          Jobs::ClientID = JobsSameClient::ClientID


          To create this, simply open Manage | Database | Relationships and drag from Jobs::ClientID out past the borders of the TableOccurrence border and then back to the same field. A dialog box will pop up where you can name this new TableOccurrence "JobsSameCLient". (Note: you haven't actually created a new table--just a new reference to the Jobs table.)


          Now base your portal on this "self-join" relationship.

          • 2. Re: Showing a client's other jobs in a portal.

            Hi PhilModJunk,


            Brilliant. I'll give it a try when I get back into work on Monday. Thanks for your help!



            • 3. Re: Showing a client's other jobs in a portal.

              Hi PhilModJunk,


              Meant to post something last night but ran out of time.


              It works! A few palpitations when it brought up the message about creating another occurrence and I thought it was away to create a duplicate table (I know you said it doesn't really) so I said ok. Kept an eye on the record count in the Jobs table and it looks ok to me (i.e. no sudden increase in records etc) plus it now shows the other jobs per client which is such a huge help.


               In the 'Tables' section of Manage Database it just says under the 'Occurrences in Graph' - Job File, (and now...) JobsSame Client


              And the 'Relationship' bit now has a thing that looks like a copy of the 'Jobs' table with all the fields but is called 'JobsSameClient' with a relationship only to the Client ID field.


              I did a couple of screen grabs but I can't see a way of attaching them on this forum.


              Once again thanks for your help with this!






              • 4. Re: Showing a client's other jobs in a portal.

                The help files and dialog boxes in Filemaker don't do the best job of distinguishing between tables, the physical location where data is stored, and Table Occurrences, a reference to a table used to describe a specific relationship to another table or tables.


                Your Relationship Graph shows Table Occurrences, not tables. When you first define a table in filemaker, the system automatically adds a table occurrence of the same exact name to your graph. As we did in this example, we can then create as many additional table occurrences as needed to build our database.


                If you want to see what underlying table a table occurence refers to in your graph, position your mouse pointer over the arrow in the upper left corner of the Table Occurrence box. A tool tip will pop up giving the source table. To clarify things in my graphs, I often make all TO's that refer to the same table the same color.