4 Replies Latest reply on Mar 22, 2011 4:03 PM by philmodjunk

    Need Relationship/Portal/Report Help



      Need Relationship/Portal/Report Help


      Ok, I"ve already tried this solution two different ways and it's still not ideal for us.  I've searched for forum for help and made vast improvements, but I still can't wrap my mind around what I'm looking for.  I'll explain what I have need of. It's a little complex.  Feel free to give input into part of the problem or all of it.

      We are a custom printing company.  Our four main areas of product include: awards, screen printing, embroidery, and retail/blank goods.  I currently have two "work orders" set up.  One for awards, one for everything else, called GARMENTS from here out (since they often overlap - this is working for us). On each GARMENTS work order, we have multiple products, each having multiple imprints. I have created a portal with line items for each garment product, this is working fine.

      The first problem: submitting/viewing/organizing the imprints per each product is the issue. I currently have another portal with "line items" with each imprint.  This works for submitting/viewing, but not organizing.  I'm having difficulty connecting each imprint to the garments it goes with.

      The second problem: department "reports".  We need to have one entry form for the salespeople, but separate "reports" for each department (screen printing, embroidery, etc.).  I currently have a solution that does this, but it's not the best (clicking on view related records is not functioning properly)

      What I want: The most ideal solution (which I'm still working on myself).  Is to have a buttton on each garment line in the portal which will open a separate window (side bar, like a help screen - I know how to make this part happen).  The button will have a script that will either open a new imprint window OR the existing imprint window if there is one. Then, this window will have to allow for multiple imprints (I'm thinking a portal).  The part I'm struggling with in this solution is how to set up the relationships/tables/fields so that each "imprint window" will be connected to the garment and the script will recognize "new" or "existing".

      Any help you can give is much appreciated. I can upload screen shots if needed.

        • 1. Re: Need Relationship/Portal/Report Help

          The first problem: submitting/viewing/organizing the imprints per each product is the issue. I currently have another portal with "line items" with each imprint.  This works for submitting/viewing, but not organizing.  I'm having difficulty connecting each imprint to the garments it goes with.

          I understand from your next to last paragraph that one invoice can lists several garments, each in a different line item and each garment might need to list several "imprints". Could there be a case where several garments need to link to the same "imprint" also? I can imagine a case where you might have a "band booster" shirt and a "band member" shirt that both use the same logo imprint but differ in other design details.

          If I have that right, then you will need a join table between a line items table and an Imprints table. You can certainly manage this with pop up windows, but you can also do it with a "master-detail" enabled pair of portals where clicking on a button in the line item portal displays the imprint data in a second portal, if you prefer.

          The second problem: department "reports". 

          Can't suggest much here without knowing more about the reports and how you've structured your data. I can speculate that you could use either Go To Related Records or a Find performed in a script to bring up line items records in order to produce a summary report for a selected department or even a report that goups the records by departement and then computes sub totals for each department all in one report.

          • 2. Re: Need Relationship/Portal/Report Help

            @Phil - you are correct.  Sometimes multiple products will have the same imprint.  Not sure what you mean by join table.  Do you just mean a sort of "line items" table to the line items? (I may be wrapping my mind around this...)

            So, you suggested the "master-detail" button.  I'm assuming I would use a script that would make the second portal show the related information, but can you give me an idea on what that would look like?  Also, I'm assuming that means I would always have the second portal showing, it would just be blank or displaing whichever product specific information I chose at the time?

            Also, how can I have one imprint connect to multiple products?

            About Reports: I currently have one "REPORT" page which is sorted by the imprint department (so connected to each "Work Order" by it's portal of imprints...) When the person hits the button to lead to their department's report, a script runs which filters the report to only show their department. So, there could be more than one line to the same "Work Order" if it has multiple imprints from that same department. I want them to be able to click on a button in that list to take them to the specific "Work Order" it connects to.  Right now, it takes them back to the work order screen, but not the related record.  I may have this whole thing screwy...  The Report is based off the department which is selected per IMPRINT.

            • 3. Re: Need Relationship/Portal/Report Help

              Also, how can I have one imprint connect to multiple products?

              That's the key issue here that is complicating your design. A join table allows you to match many records in one table to many records in another by serving as a link between the two. Your LineItems table is just such a join table. It links your invoice table to your table of goods and services that you offer:


              You'll see this table structure in most invoicing systems including the Invoices starter solution that comes with FileMaker.

              What we need is a table for your Imprints records and a join table between it and LineItems so that multiple line items can link to the same imprint and so that multiple imprints can link to the same line item.


              LineItems::_pk_LineItemID = Imprint_Item::_fk_LineItemID
              Imprints::_pk_ImprintID = Imprint_Items::_fk_ImprintID

              To implement this with a master-detail pair of portals (your line items portal is the "master" here) will take some more work and it may be that a pop up window may work better for you. I have to break for lunch now, but will check back later with more details unless another poster steps in and suggests a way to do that.

              • 4. Re: Need Relationship/Portal/Report Help

                I didn't want to dive into a description of how to get a master-detail pair of portals working with the added complication of an intervening join table without testing the details in a demo file. When I finished, I realized that the demo file explains this technique better than I can by just typing in a long post.

                I'm going to just describe the main points, please download the demo (click the blue download button), look it over and post back if you don't understand how/why a part of it works:

                In manage | Database | relationships, there are two table occurrences of the LineItems_Imprint join table and two of the Imprint table.

                The top chain of relationships documents the classic many to many relationship with join table that's typically used to implement such a relationship. The lower chain, is a selectable relationship that links to a specific set of join records based on what LineItemID value is entered in the global gSelectedLineItems field. The line items portal requires the upper chain, the Imprints portal requires the lower chain of relationships.

                There are three script triggers set to make this work smoothly:

                OnRecordLoad clears the global field to keep imprint records from a different invoice from being listed in the Imprint "detail" portal.

                OnObjectEnter triggers on the two editable line item fields update gSelectedLineItems to get the LineItemID number from that portal row. To see the Imprint records for a given line item row, click in either field.

                A crude conditional format highlights the line item portal row to help show which line item records are linked to the currently displayed imprint records.

                To add a new imprint, click the New Imprint Button, it runs a script that creates both a new imprint record and a new Join record to link to it. To select an existing imprint, the imprintID field is formatted with a conditional value list that lists all existing imprints for this invoice. A different value list could be used to list all imprints in the database if you preferred that option.

                The LineItemID fields in both portals and the global field are visible on the layout just so you can see how things work. If you removed them from the layout, everything would still work the same.