11 Replies Latest reply on May 7, 2014 10:08 AM by erolst

    How can I select from a list of line items?

    paulwatts

      Hi all.

      I have a set of related tables "Customers", "Jobs", "Invoices". These are related as Grandparent, Parent and Child. I also have a table "Job Items" which is a child of the "Jobs" table.

      My "Jobs" layout has a list of job items and prices which are entered via a portal on the layout. Each item and price is job specific, i.e. is NOT from a standard list of products/prices.

      I want to be able to select (item and corresponding price) from this list when preparing my invoice (via the "Invoices" layout) thereby allowing me to invoice different sets of items on different invoices for the same job.

      For the life of me, I cannot figure out how to do this. I have tried drop-down boxes and value lists and have tried every variation of relationships I can think of. Nothing seems to work.

      Can you help me please?

       

      Regards

       

      Paul

        • 1. Re: How can I select from a list of line items?
          beverly

          I do this with a scripted button. The script parameter gets the unique record ID field of the line item record. With that you can place it where needed.

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: How can I select from a list of line items?
            erolst

            Assuming that you don't want to invoice the same item multiple times, the invoiceID is a direct attribute of a jobItem record.

             

            On your Invoices layout, display a portal into JobItems based Jobs::invoiceID_FK = JobItems::invoiceID_FK, i.e. show all jobItems of the job of the invoice.

             

            Then use a button in the portal to assign the current invoiceID to each portal row jobItem you want to charge with this invoice.

             

            Use Conditional Formatting to indicate that a jobItem is associated with this invoice, or has already been assigned to another one, and decide how to proceed in your script in case of a pre-existing invoiceID (e.g. cannot re-assign if invoice of pre-assigned ID has already been sent/charged etc.).

            • 3. Re: How can I select from a list of line items?
              paulwatts

              Thanks Beverly.

              I appreciate this very much and will have a go at implementing your approach.

               

              Paul

              • 4. Re: How can I select from a list of line items?
                paulwatts

                Thanks erolst

                I understand and appreciate your detailed response and will now attempt to implement your approach which expands on Beverly's.

                 

                Paul

                • 5. Re: How can I select from a list of line items?
                  paulwatts

                  Hi erolst and Beverley

                  Before attempting to develop a script, as you have both suggested, I have been trying to develop a solution that uses Look-ups.

                   

                  I have tried to get my "Invoices items" table to look-up from the "Jobs items" table. All the relevant Job items show up in the Invoices items table but when I edit this table via a portal, the corresponding Job items change too (and vice-versa). In other words, the relationship appears to be as normal instead of Look-up. This may be due to me using portals. Any comment? Do you have any experience of this?

                   

                  Regards

                   

                  Paul

                  • 6. Re: How can I select from a list of line items?
                    erolst

                    Don't know what you mean by "lookup"; the relationship you're trying to lookup via is probably the one you need to establish in the first place – hence the need for scripting.

                     

                    I was more thinking along the lines of this little example attached:

                    1 of 1 people found this helpful
                    • 7. Re: How can I select from a list of line items?
                      paulwatts

                      Hi erolst.

                      Thanks again for your response. I was simply checking out one more available option before I took the plunge of writing a script.

                       

                      By "Look-up" I simply mean the standard "Look-up" facility that Filemaker provides to allow look-up of the value of one field and placement of that value in another field. This is an option that can be selected in the Manage > Database > Fields > Options window. The advantage is that the value placed in the new field can be editted without affecting the value in the original field. However, although this worked perfectly well on a simple example, I couldn't get it to work when applied to my problem. I don't know why. By the way, I am using Filemaker Pro 13.

                       

                      Thanks for your example. I understand what you have done in principle and will attempt to write a script to do the job.

                       

                      Best wishes

                       

                      Paul

                      • 8. Re: How can I select from a list of line items?
                        paulwatts

                        Hi erolst

                        I'm a bit confused about how to relate my tables. Are you suggesting:-

                         

                        1. Jobs is related to Invoices through the field JobsID (one-to-many).

                        2. Jobs is related to Jobitems through the field InvoiceID (many-to-many) as well as through the field JobsID (one-to-many)?

                         

                        Paul

                        • 9. Re: How can I select from a list of line items?
                          erolst

                          paulwatts wrote:

                          I'm a bit confused about how to relate my tables. Are you suggesting:-

                          1. Jobs is related to Invoices through the field JobsID (one-to-many).

                          2. Jobs is related to Jobitems through the field InvoiceID (many-to-many) as well as through the field JobsID (one-to-many)?

                           

                          Well … all of the above. Why not test these relationships in natural language and/or see what purpose they serve?

                           

                          1. One job can have many invoices, and each invoice belongs to exactly one job (as usual)

                           

                          2. a) Relating Jobs to JobItems via one (or more) selected invoiceIDs is just a convenience relationship for the UI; but then it's still Jobs --< JobItems (n-m), because even if you'd use a multi-key, it would only be one job record.

                           

                          2. b) One job can have many items, and each item belongs to exactly one job (equally run-of-the-mill)

                           

                          In short: distinguish between (at least) two types of relationships:

                           

                          structural, i.e. match a PK against its counterpart FK (Jobs --< Items, Jobs --< Invoices, Invoices --< Items; and of course Jobs --< Invoices --< Items), and those that are

                          created for workflow/UI purposes and use utility fields:

                          e.g. Jobs::selectedInvoiceID = Items::invoiceID has the same source and target (tables) as Jobs --< Items and Jobs --< Invoices --< Items, but will use a different route (TOs) with a different intent.

                          1 of 1 people found this helpful
                          • 10. Re: How can I select from a list of line items?
                            paulwatts

                            Sorry erolst but I'm relatively new to this game. You are doing a great job of steering me through this but I still have a little way to go.

                             

                            A. What is UI?

                             

                            B. I understand what you are saying about structural relationships. I've put the structural relationships in place (Jobs<Invoices, Invoices<Items, Jobs<Items). On my Invoices layout, all the items for the selected job appear in the Jobitems portal for each invoice raised. Where I have raised 2 invoices for a single job, all items for that job appear against both Invoices.

                             

                            C. I'm now trying to get my head around what you call "work flow purposes". I need to find a way of limiting the Jobitems that appear in the Invoice layout portal to only those for which the InvoiceID appears in the Jobitems table. You seem to be advising me to relate Jobs::InvoiceID to Jobitems::InvoiceID. When I do this, all Jobitems disappear from the portal (for all Invoices) irrespective of whether the Invoice ID value appears in the Jobitems records. Can you help here?

                             

                            D. I will try to deal with the issue of producing a script to put the InvoiceID in the appropriate Jobitems records later, hopefully, by myself.

                             

                            Regards

                             

                            Paul

                            • 11. Re: How can I select from a list of line items?
                              erolst

                              UI = User Interface.

                               

                              The thing you have to keep foremost in mind when working in FileMaker is context (as implicitly determined by the current layout, or explicitly selectable in calculations).

                               

                              The sample file manages invoices from the context of a Job. Depending on how much additional information you need to add to an invoice (select invoicing address and boilerplate texts, add a comment, apply a discount etc.), that's a resonable approach.

                               

                              There are many cases where a table merely serves as a container, providing data to its parent(s), without having a UI layout of their own.

                               

                              If you decide on using a dedicated editing layout for Invoices (as seems to be the case), you need a relationship Invoices::invoicePK = JobItems::invoiceFK.

                               

                              If on this layout you want to be able to assign arbitrary items (of the job) to an invoice, create a relationship like Invoice::jobFK = JobItems_ofJob::jobFK which allows you to create a so-called picker portal.

                              (Translated: show all items of the job this invoice belongs to.)

                               

                              Then add a button to the portal row that sets JobItems_ofJob::invoiceFK to the PK of the current invoice record (or resets it).

                               

                              I think the sample file gives you enough fodder to experiment; just remember context when you adapt the scripts to other layouts.