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:
Work Tickets::Invoice Number = 0 and Work Tickets::Contractor=Invoices::Contractor
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!