4 Replies Latest reply on May 2, 2012 12:35 PM by VeronicaDaigle

    Stuck with portals! Would appreciate help :)



      Stuck with portals! Would appreciate help :)


      Ok I am new to using File maker so please bear with me; I had posted a related question earlier but now am having trouble actually implementing the suggested solution correctly.

      I have a database for keeping track of all of our project information on a daily basis with numerous tables. I have one table for all contractors working on the project, one table for each project location we are tracking one for scheduled tasks etc. The contractors, locations, and task tables are related to several other tables so that when the user is filling in data in other tables they must choose a contractor, location or task from those already entered into the database in the related table.

      Now I want to set up a portal using relationships between 3 tables in particular. Every day the field staff will enter in a work ticket from each contractor on the site into a Work Ticket database. The work ticket table has a field for Contractor Name which is related to a unique Contractor Name in the contractors table, so each contractor will have multiple related tickets. On the Work Ticket table there is also an invoice number field which the user will leave blank, as the invoice for each contractor may not be received for several days or even weeks.

      Now the part I am having trouble with. There is another table which keeps track of invoices. The Invoice table also has a field for Contractor Name, which is related to the Contractor Name field in a second occurrence of the Contractors table.  The primary key of the invoice table is the Invoice Number. Now each invoice that is received is for work recorded on multiple work tickets for that contractor. I set a relationship between the Invoice Number field on the Invoice table and the Invoice Number field on the Work Ticket table, which is still blank for any tickets that have not yet been invoiced. I created two portals which are to show the related records from the Work Tickets. I want the first portal to be filtered to display only those Work Ticket records for which the contractor on the work ticket and the contractor on the invoice are the same, and for which the invoice number on the Work Ticket is still blank. I want the second portal to show records only for which the Invoice Number field on the Work Ticket is equal to the Invoice Number for the invoice.

      I initially set up the filter criteria on the two portals as follows:

      Each portal record will be visible when:

      Portal 1

      Work Tickets::Invoice Number = 0 and Work Tickets::Contractor=Invoices::Contractor

      Portal 2

      Work Tickets::Invoice Number = Invoices::Invoice Number


      When I tested the portals (I created a test Work Ticket with Contractor=Test Contractor and Invoice Number=blank, then created an invoice with the same contractor name), no records were displayed. I tried creating a relationship between the Contractor field on the Invoice Table and the Contractor field on the work ticket, I removed the relationship between the invoice numbers (although I still do want there to be a correlation between them) and changing the filter criteria for Portal 1 to Work Tickets::Invoice Number = 0, and the record showed up in Portal 2 instead of Portal 1. I tried making the relationship between both the Contractor and Invoice Number simultaneously on the Invoice and Work Ticket tables, and was back to no records appearing. The final goal of the two portals is to have the user click the work tickets in the first portal which are on that invoice and have a script set the blank invoice number on that work ticket to equal the invoice number of that invoice, but I can’t even get the portals to work right. I am still learning the program and how to work with relationships and databases, so perhaps I am misunderstanding something simple and someone might be able to help me out on this.

      Sorry this is so long but I am really stuck!

        • 1. Re: Stuck with portals! Would appreciate help :)

          I vaguely remember a post about work tickets and invoices. I do not remember the details...

          For start, what are the relationships between the layout's table occurrence and the two portals' table occurrences?

          A table occurrence is a "box" found in manage | Database | relationships connected to other table occurrences by relationship lines. The layout's table occurrence is specified in Layout Setup | "Show Records from". The portals' are shown in Portal Setup | "Show Related Records From".

          I suggest you also remove all portal filtering (Or clear the check box to disable the filter) first and see what records, if any appear in the portal. Any filter you define will take the records you now see and reduce them to only those that evaluate as true for your filter expression.

          For portal two, if your layout is based on Invoice, you should not need any filter at all as you should have a relationship that matches invoice numbers in these two fields anyway.

          You may need to create additional table occurrences for Work Tickets so that you can define different relationships for your two portals.

          • 2. Re: Stuck with portals! Would appreciate help :)

            There is only one occurence of the work tickets table and one occurence of the invoice table. There are two occurences of the contractor table. When I remove the filters on both portals and the relationship between Work Tickets and Invoices is only based on the Contractor field and there is no relationship between the invoice field the records show up in both portals.


            If I make a second occurence of the Work Tickets table to create a relationship between invoice numbers will both occurences be updated when the user adds a new work ticket through the work ticket layout? And when an invoice number is added to a work ticket will that invoice number be added in both occurences as well? Then I would no longer need the filters for the second portal and I would have the invoice number relationship I need, and my only problem would be creating a filter to only show blank invoice numbers for the first portal. Is there a reason Work Tickets::Invoice Number = 0 filters out the work ticket even though the invoice number is blank? Is my syntax incorrect?

            Thanks for the quick reply!

            • 3. Re: Stuck with portals! Would appreciate help :)

              Seems like the relationship should be based on the invoice number for at least one occurrence of Work Ticket--that way you can see either a portal listing all work tickets paid off by a given invoice or you can use a list view layout to list all work tickets for a given invoice and the layout can then include fields from the invoice table to provide a complete report or invoice.

              Occurrences are just "labels" that allow you to refer to the same data source table (hover the mouse over the top left corner to see an occurrence's data source table), in different relationships. Any data changes are made to the data source table that they have in common so changes in one will automatically be reflected in another.

              As a filter for finding records with an empty field, use IsEmpty ( PortalTableOccurrence::Field ).

              • 4. Re: Stuck with portals! Would appreciate help :)

                Works perfect now thanks! Now onto writing the script :)