7 Replies Latest reply on Jan 27, 2011 2:53 PM by philmodjunk

    Need many to many help (maybe just a hint)

    JeffDening

      Title

      Need many to many help (maybe just a hint)

      Post

      I am making a customer tracking system and running into some difficulty in understanding the mechanics.  I have discarded using pre-fab templates as I would require enough customization and tweaking that I may as well do it myself from scratch.

      I have a Work Order table and a Repair Task table that (to my understanding) should be in a many-many relationship.  I have made a Line Item join table to fit in between but...

      1. Do I need the line item table to have a new record to correspond with each new Work Order?

      2. If so, what sort of fields should I focus on to make the relationship through?  I keep ending up with a relationship graph that is anything but many-many with a join table in the middle.  Maybe the visual of the graph doesn't matter as long as it works?

      I have many other questions but those 2 will be a good starter.

      Thanks

      JD 

        • 1. Re: Need many to many help (maybe just a hint)
          philmodjunk

          Is this your situation?

          Any given work order can list multiple standard repair tasks. Each time you need to work on a customer's machine, you create a new work order record and use line items to itemize each repair task for that machine. Standardized info for each repair task is listed in Repair tasks. Thus, you place a portal to line items on your work order layout and use a drop down in the portal to specify a repair task. Selecting a task from this drop down causes data from Repair tasks (description, man hours, etc.) to be copied into fields in the portal row.

          If so, then this is really just a standard Invoicing situation with different names. With a typical invoice, you list items purchased in the portal and a drop down in the portal is used to look up description, unit pricing, etc. from a products table. It's just that in your case, your invoice is a work order and your products are repair tasks.

          Assuming I have that correct, you might examine this demo file created by Comment and uploaded to a different Filemaker forum:  

          http://fmforums.com/forum/showpost.php?post/309136/

          Just think of the invoice table as your Workorder table and Products as your RepairTasks table.

          • 2. Re: Need many to many help (maybe just a hint)
            JeffDening

            Yes that is my situation.

            After looking at the link you shared it gives me something to study but also tells me I need to read up a lot more or play around a lot more figuring out how things work and relate to each other.

            The question I can't reconcile in my head (probably can't see the forest for the trees) is if the task or product table feeds into a portal in the line item table, how does the data get into the work order or invoice table?  In my elementary experiments in FM I have found that if you don't reference data as coming from the proper source it doesn't cooperate too well.

            Thanks a bunch

            JD

            • 3. Re: Need many to many help (maybe just a hint)
              philmodjunk

              if the task or product table feeds into a portal in the line item table, how does the data get into the work order or invoice table?

              It doesn't. It stays right there in the LineItems table. Any time you are working with an invoice and you need to see its line items you add the portal and the relationship the portal is based on brings up the correct data in the portal. Anytime you are working with a line item record and need to see data from its invoice, you can simply place the fields from the invoice table on your layout and this same relationship will bring up the related data from the invoice. That's how this demo file prints an invoice from a layout based on LineItems, BTW.

              • 4. Re: Need many to many help (maybe just a hint)
                JeffDening

                So for every unique work order I also create a unique line item record?  I was playing with reverse engineering some pre-fab FM solutions and got the idea of temp files stuck in my head.  I think that's where one of my hang ups is in getting my head wrapped around this.  

                I did not fully comprehend your previous post.  Any time you are working with an invoice and you need to see its line items you add the portal and the relationship the portal is based on brings up the correct data in the portal.  This sounds like in the Invoice table a portal is created referencing Line Items.  Wouldn't this be a portal of a portal since the pertinent data appearing in the Line Items Table is in a portal itself?  I am afraid the rudimentary experience I have is not nearly enough for what I want it to do. 

                Apologies if I seem a little thick.  I am much better visual/spatial than interpreting text.  Plus, I need to understand fully how and why something works the way it does before I feel comfortable with it.  I have many more questions and much more confusion but I am going to go read some more and see what I can figure out. 

                Thanks again

                JD

                • 5. Re: Need many to many help (maybe just a hint)
                  philmodjunk

                  So for every unique work order I also create a unique line item record?

                  For every work order you would create as many line item records as you need to list all the repair tasks for that work order. Each repair task will be listed on a different row of the portal and thus in a different record in LineItems. I think you need to read up on Portal in the help file, that may help you fill in some back ground here.

                  On to paragraph 2 of your last post...

                  A portal is an object you put on your layout so that you can work with from 1 to many records from a related table. In this demo file, the Invoices layout has a portal to the line items table. The relationship matches all records in LineItems with the same number in LineItems::InvoiceID as the current Invoice record's InvoiceID. This is how the portal works to control which lineItems (Repair tasks in your project) are associated with a given Invoice (work order in your case) record. Any time you have a layout based on Invoices, you can use a portal to LineItems if you need to see those records.

                  Please make sure that you open the demo file, check out Manage | Database | Relationships and compare what you see there to the invoices layout where you'll find a portal to line items. The options selected in Relationships, controls the function of this portal as it is based on LineItems.

                  • 6. Re: Need many to many help (maybe just a hint)
                    JeffDening

                    Conceptually I think something clicked now that I have purged temp files from my thoughts.

                    Tell me if I am right--

                    I have work order X.  The repair tasks used on the single work order X were 1, 2, 5, and 72.  I will need 4 individual line item records (one for 1, one for 2, one for 5, and one for 72) to be able to do what I need to do.  Depending on the needs of the individual work order I could have 1 line item record or I could have 342.  Am I getting it?

                    Edit:  Looks like we posted at the same time and I am on the right track

                    • 7. Re: Need many to many help (maybe just a hint)
                      philmodjunk

                      Yep, sounds like you got it.