7 Replies Latest reply on Sep 8, 2014 10:55 AM by FentonJones

    Relational issues

    RelativeNewbie

      Title

      Relational issues

      Post

      I have a database with very many tables.  Most of it is working but right now I'm having problems with the Purchase Orders.  I have similar problems with the Time Sheets but any assistance with this problem would probably solve the time issue too.

      Problem outline:

      I have a PURCHASE ORDER table and a PRODUCT table which are joined via a linking table called LINE ITEMS.  I have the LINE ITEMS portal on the PURCHASE ORDER layout but want to reference 2 other tables in the portal row.  The 2 other tables being PROJECT and JOB.

      The portal fields are as follows:

      Item #          Project #           Job #           Product #            Product Description          Qty           Cost          Extended Price

      Also the Job# must only display Jobs relevant t the Project selected.  

      I have also attached a screenshot of my relationship graph.  Any advice would be most appreciated.

      Newbie.

      Relationship_Table.jpg

        • 1. Re: Relational issues
          dbail22@comcast.net

          If I understand your question the problem is a portal can only contain one table relationship.  All you can show in a portal are fields from that table you link.  You could put calculations or lookups in the second file that would get their data from the third file.  Then that field could be included in your portal.  You cannot have portals within portals.

          • 2. Re: Relational issues
            RelativeNewbie

            Hi dbail,

            Thank you for your response.  Could you give me some idea as to what the calculation would be?  I'm wanting to be able to report on the Project and Job related Purchase Orders.

            I'm not joking when I say I'm a Newbie, I'm still running on my 30 day trial here :)

            Thanks
            Newbie

            • 3. Re: Relational issues
              FentonJones

              I wrote an earlier post, then deleted it. I really think now that you really need another Table Occurrence (TO) on your Relationship Graph; actually you need 3 new TOs, added to the Purchase Order (PO), area:

              Purchase Order needs 2 new fields:
              CustomerID
              ProjectID

              TOs (new):
              Purchase Order -> Customer (new)
              Purchase Order -> Projects (new) -> Jobs (new)

              [ Projects (new) -> Jobs (new) have the same link as the original. Select both, the Duplicate before moving to Purchase Order (then they'll remain linked to each other as was).]

              This way you'd have a correct link to the Customer, rather that depending on the link from Vendor ->Contacts (which is not reliable if the Vendor has any other Customers linked to a Vendor, correct?).

              Once the PO::CustomerID is chosen [or set via a script from Customer list layout {or similar)] then you could chose the lasted Project (or have it auto-entered the latest Customer's Project). 

              I am assuming 1 (and only 1) Project belongs to a Purchase Order, and that ALL its Jobs also. Otherwise you would NOT keep the Jobs (new) TO linked to Purchase Order TO. You'd link it to the Order Line Item, and add:
              JobID
              to the Order Line Item table to link it.

              In fact, if more than 1 Project can be on 1 Purchase Order, then you'd NOT link it to Purchase Order either. You'd add:
              ProjectID
              to the Order Line Item table instead, and link Project (new) to that instead.

              I don't know the answers to those questions. I'm not really a "business" person. But I do know that it won't be hard to link it correctly once we know.

              Making it "user friendly", with either Value Lists or Portals, or list in on a layout (on a new window or such) are also needed. But get the Relationship Graph correct first. When doing this second part, question, are you using FileMaker 13? I'm not, too old :-0

              • 4. Re: Relational issues
                RelativeNewbie

                Hi Fenton,

                Thank you for getting back to me.  I had read your first comment and it went straight over my head :)
                To answer your question, YES one Purchase order can have more than one Project related.  I've created new TO's as follows:

                Project Purchase Order::ProjectIDpk-------------<OrderLineItems::ProjectIDfk
                Project Purchase Order::ProjectIDpk-------------<Job Purchase Order::ProjectIDfk (this value list says to only show related records from Project Purchase Order and is working fine)

                This seems to be working fine but now I've realized I need it to show only the Projects where "completed" field is marked "No".  

                Do you know how I would address this?  Sorry to be a bother.

                Regards
                Karen

                • 5. Re: Relational issues
                  FentonJones

                  OK. But I cannot tell exactly what you've done, by reading your TO names. And you've now specified more about what is needed.

                  Since you can have more than 1 Project within a purchase, the Purchase Order table is NOT going to be linked to the NEW Project table. Only a NEW CustomerID field will be added to the Purchase Order table.

                  Purchase Order::CustomerID = Customer (new)::CustomerID

                  The rest happens in OrderLineItems (OL).

                  OrderLineItems::ProjectID = Project (new)::ProjectID
                  OrderLineItems::JobID = Job (new)::JobID

                  The ProjectID would be useful for a Value List, to pick which Jobs for the OL.

                  P.S. I use a different full method for naming. I'd use something more like:

                  PU__Purchase Order
                  pu_Customer

                  pu_OL__OrderLineItems


                  It may look longer. But it says what it is better. It also sorts correctly when trying to choose from a list of ALL the TOs (of your TO group); which matters if you're adding something to a script. Example, this is how the above will sort (I think): 
                  PU__Purchase Order
                  pu_Customer
                  pu_OL__OrderLineItems
                  pu_ol_Job
                  pu_ol_Project

                  [ Yes, "project" is after "job". We could link Job to Project, then it wouldn't. But linking them separately to OL seems more direct, to match data; up to you.]

                  • 6. Re: Relational issues
                    philmodjunk

                    This seems to be working fine but now I've realized I need it to show only the Projects where "completed" field is marked "No".  

                    This statement and several others suggest that you might be trying to set up a conditional value list. If you are seeking more info on how to set up such value lists, see this file on the subject: https://www.dropbox.com/s/8phiqrgpts4drre/Adventure%201%20CVLs.fmp12?dl=0

                    It covers 8 different ways to set up a conditional value list.

                    • 7. Re: Relational issues
                      FentonJones

                      Phil has a great file there. You should be reading it right now :-!

                      Projects where "completed" field is marked "No"

                      I didn't specify anything in my earlier post. But you just add the line to match "completed" is "no" to the relationship. [ Or ≠ "yes", whatever that is ]
                      OrderLineItems::ProjectID = Project (new)::ProjectID

                      Which means you'd need a field in the OrderLineItems (OLI) table which could match "no". This would likely be a calculation field, which would be the same in all records (result like, "no").

                      Also however, be aware that if you ONLY change this relationship then you will NOT be about to see any "completed" Projects from the OrderLineItems table (as that's the only decent relationship to Projects from OLI).

                      Since the Purchase Order and their OrderLineItems are pretty important, and you'll want to see their data AFTER the Projects are "completed", then you'll really need to see them WITHOUT the "completed" is "no". So you'll need BOTH relationships linked to OrderLineItems. 

                      An alternative method would be to create field(s) in the OrderLineItems table which could "Lookup" (or enter by Calculation) any data you need to "see" from Projects, after "completed" is not "no" no more (I just had to say that :-).