11 Replies Latest reply on Jan 4, 2010 2:13 PM by philmodjunk

    script to get user selected records associated with invoice ID

    mdscarpa

      Title

      script to get user selected records associated with invoice ID

      Post

      I am developing a database, have a customer table, jobs table, and invoice table so far. It is for a service business, landscaping specifically, so customers are usually billed monthly. In my jobs table I have a field called "invoiced?" when the job is created this field is automatically set to pending. I plan on creating a layout using a sub-summary report sorted by customer to list all jobs by customer where invoiced? = "pending". I want to place a button next to each customer name that I can click and it will assign all the related records (that fit the criteria of invoiced? = "pending" and possibly date constraints) to a single invoice. I figured the best way to do this would be to associate them through a realationship where clicking the invoice button would assign the same invoice number to all the pertinant records for that customer. I am fairly new at scripting and do not really know where to start with this. Also should I create the report layout under the jobs table or should i create the layout as a portal in the invoice table? Thanks in advance.

        • 1. Re: script to get user selected records associated with invoice ID
          bcooney
            

          Do jobs have several services associated with them? If so, do you show the service line items on an invoice, or just the job? Can a job's service items (if there are any) be split across invoices? If so, then a job can be on more than one invoice. You didn't ask about invoice payments...but are they paid in full or can there be partial payments, and can payments split across invoices?

           

          I'd need to know the above before I propose a design. I could assume everything is very simple, but then your structure will fail you down the road.

          • 2. Re: script to get user selected records associated with invoice ID
            enrluc
              

            Hi,

            I have a very similar problem.

             

            Scenario: I have multiple Job Activities that can be billed with a single Invoice. Every activity has a field referencing the invoice that bills the activity. If this filed is empty then the activity is not billed yet.

             

            When I create a new invoice I want to display all the activities that are not billed, select them (using a checkbox) then clicking a button to setup the relation (the selected activities are now billed by the current/new invoice).

             

            I'm new to filemaker but not to programming or databases.

             

            Currently I don't know how to:

             

            - list detail records [activities] that are NOT related to the master record [Invoice] ( when they're related I use a portal)

            - Put in a portal row a checkbox that the user can select.

            - Observe the status of this interface checkbox in a script.

             

            thank you in advance 

            • 3. Re: script to get user selected records associated with invoice ID
              philmodjunk
                

              "- list detail records [activities] that are NOT related to the master record [Invoice] ( when they're related I use a portal)"

              The answer here is to make them related, you can define a different relationship and add a different portal

               

              Many developers use a different approach here. Instead of check boxes, they define a drop down list of Line items. Selecting the line item from the two column value list creates a new line item record and enters the ID number of the selected line item into a new row in the portal. Description and pricing data then automatically appear in the row via look ups and/or auto-entered calculations. I mention this approach because it takes less work to set up.

               

              If you want to make the effort to use the check box approach, you'll need a script to create a set of related, unbilled line items each time you create a new invoice. This can be a very simple looping script.

               

              You can set up a simple field as a check box field.

              Define a value list with a single value such as the number 1.

              Place your field on your layout and use field control setup... to format it as a check box field that uses the above value list. Resize your field so that the 1 is not visible.

              When a user clicks the check box, a 1 will be entered into the field and the box will show as checked. You can include this field in relationships to reference just billed items (such as you'll need for computing the invoice total bill).

               

              Such a relationship might look like this:

              Invoice::ID = LineItems::InvoiceID AND

              Invoice::Billed = LineItems::Billed

               

              LineItem::Billed would be your check box field and Invoice::Billed would be a calculation defined to produce the matching value:  1.

               

              "- Observe the status of this interface checkbox in a script."

              You may not have to if you use the right relationship, but If [checkboxField = 1] will check the above number field to see if it is selected. (Can be simplified to just if [CheckBoxField] in the above example. Keep in mind that if you have a check box field with multiple values, a more sophisticated expression will be needed.

              • 4. Re: script to get user selected records associated with invoice ID
                mdscarpa
                   each customer can have multiple jobs, and each invoice can have multiple jobs, so i think i have my relationships good, i dont need a joining table for a many to many..  i created 4 payment fields, generallly customers pay all at once but just in case i created 4, and then a remaining balance field which subtracts the payments from the amount due, once that is zero my invoice is marked paid. i figured it out with alot of thinking and turned it into a script. basicallly the script goes and creates a new invoice, sets the primary key as a variable, and sets the foreign key of all the invoices selected to that of the invoice primary id. only problem i have now is i can only invoice one customer at a time because if i select another customers jobs at the same time it doenst know enough to create a seperate invoice. I'm sure with some scripting this could be worked out, but this is my first database so I am trying to learn by building one for my two companies.
                • 5. Re: script to get user selected records associated with invoice ID
                  philmodjunk
                    

                  " ...i created 4 payment fields, "

                  If you used a portal where each row was a single payment, your design would be much more flexible and would likely simply your needs to support this with scripts.

                  • 6. Re: script to get user selected records associated with invoice ID
                    enrluc
                      

                    PhilModJunk wrote:

                    "- list detail records [activities] that are NOT related to the master record [Invoice] ( when they're related I use a portal)"

                    The answer here is to make them related, you can define a different relationship and add a different portal

                     

                    Many developers use a different approach here. Instead of check boxes, they define a drop down list of Line items. Selecting the line item from the two column value list creates a new line item record and enters the ID number of the selected line item into a new row in the portal. Description and pricing data then automatically appear in the row via look ups and/or auto-entered calculations. I mention this approach because it takes less work to set up.

                     Thank you for replying. This is much simpler, actually I use this approach in the different activity/expense scenario that I use to track the expenses for an activity.

                    In this case I have a master (activity) layout with a portal (expenses) I use to display current expenses related to the activity and to add new ones. 

                    But I can only ADD new expenses or view the expenses that are already related to the activity. If I already have an unrelated expense and I want to relate it with an activity I cannot (or I don't know how to) relate it to an existing activity.

                    Can I do this using a portal: relate an already existing detail row using the master portal?  (an existing expense in a row of the activity portal?) When I set a relationship I can only set to "add a row using this relationship" and when I try to add the existing expense in the portal row I get a duplication error.

                     

                    This is the problem I have in the invoice/activity scenario I described in my first post, infact the activity is present in the database BEFORE the invoice is created.

                    • 7. Re: script to get user selected records associated with invoice ID
                      philmodjunk
                        

                      Sometimes it can be difficult to visualize another person's design via this forum and this is definitely the case here.

                       

                      "If I already have an unrelated expense and I want to relate it with an activity I cannot (or I don't know how to) relate it to an existing activity."

                      The above statement seems to be the heart of the manner. To relate an existing record in one table to an existing record in another table, their match fields must be assigned matching values. That's what's happening in my previous post with the check box fields.

                       

                      You'd have two relationships pointing to two different table occurrences that have the same data source table:

                       

                      Invoice::ID = LineItems::InvoiceID

                       

                      would give you all invoiced line items whether they are "billed" or not.

                       

                      Invoice::ID = LineItems 2::InvoiceID AND

                      Invoice::Billed = LineItems 2::Billed

                       

                      gives you all line items where you've clicked the check box to bill the item.

                       

                      Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

                      Table vs. Table Occurrence (Tutorial)

                       

                      • 8. Re: script to get user selected records associated with invoice ID
                        enrluc
                          

                        Hi PhilModJunk,

                         

                        there was a misunderstanding. 

                         

                        in my last post I was referring to the "drop down list" solution, not the "checkbox" one. i think that the drop down solution is better when you have few details where the checkbox is better when you have many.

                         

                        So now I talk about the drop down solution.

                        I imagine a layout in the context of Invoice where I have an invoice record and a portal for related activity records. I have a relationship Activity::InvoiceID = Invoice::InvoiceID and I've set "add records through this relationship" on the Activity side.

                        Just for sake of clarity Activity::InvoiceID is a foreign key that references Invoice. When Activity::InvoiceID IS NULL it means that an activity exists in the database but that it is not yet billed by any invoice.

                         

                        Suppose I have an activity with Activity::ActivityID = 100 already in the database and I want to relate it with an Invoice. 

                        I begin creating an invoice with no activities related. Then I move in the portal and I try to set the field linked to Activity::ActivityID to 100. When I do this I got a duplication error because I'm trying to create a NEW activity with the primary key of an existing record.

                        (if I try to set an new Activity::ActivityID [that is not in the database] then a new activity is created and it's automatically related to my invoice).

                         

                        so in my experience I can create new detail records using a portal but can't set a relationship with an existing detail record using the portal.

                         

                        Am I wrong? It's possible to relate an existing detail row with a master row using a portal? 

                         

                        • 9. Re: script to get user selected records associated with invoice ID
                          philmodjunk
                            

                          You can, it just won't be the same relationship. You would use one relationship with a portal to show all invoiced records. Clicking the check box assigns a value to it which makes the second relationship (all billed, invoiced records) valid for that record. A list of just the billed, invoiced records would require either a second portal or a "filtered portal".

                           

                          " i think that the drop down solution is better when you have few details where the checkbox is better when you have many."

                          I would say the exact opposite is true.

                          • 10. Re: script to get user selected records associated with invoice ID
                            enrluc
                              

                            Hi,

                             

                             this is my solution. Basically I added a new field to my table with a single value of 1 for the checkbox. 

                            The user is presented a page with all activities that are not yet related to an invoice. He can check the ones that wants to add to the invoice then clicks a button with the following script. 

                             (table names are in italian)

                             

                            I first create a new invoice and save the new invoiceID in a variable.

                             

                            Go to Layout [ “Gestione Documenti” (Documenti) ]

                            New Record/Request

                            Commit Records/Requests[ No dialog ]

                            Set Variable [ $IDnuovoDocumento; Value:Documenti::ID_DOCUMENTO ]


                             

                            Now I find all activities that are checked 

                             

                            Go to Layout [ “Prova DettaglioIncarico” (DettaglioIncarico) ]

                            Enter Find Mode [ ]

                            Set Field [ DettaglioIncarico::checked; "=1" ]

                            Set Field [ DettaglioIncarico::ID_DOCUMENTO; "" ]

                            Perform Find [ ]

                             

                            Now, for each record I set the Activity::InvoiceID to the new invoice and deselect the checkbox. I also calculate the total income of the activities and set it in the new invoice

                             

                            Set Variable [ $totale; Value:0 ] 

                            Enter Browse Mode

                            Loop

                               Set Field [ DettaglioIncarico::checked; "" ] 

                               Set Field [ DettaglioIncarico::ID_DOCUMENTO; $IDnuovoDocumento ]

                               Set Variable [ $totale; Value:$totale + DettaglioIncarico::IMPORTO ]

                            Go to Record/Request/Page[ Next; Exit after last ]

                            End Loop Go to Layout [ “Gestione Documenti” (Documenti) ]

                            Set Field [ Documenti::IMPORTO; $totale ] 

                             

                             Now I have 2 more questions.

                             

                            1. Do I need to add the checkbox as a new field in a table? It is possibile to have an "user interface" checkbox (something that it's in the layout, but not in the database and that I can control from a script?

                             

                            2. can I do the same script in SQL? The script is just 3 SQL queries (and I'm more familiar with SQL than with filemaker scripting) 

                            • 11. Re: script to get user selected records associated with invoice ID
                              philmodjunk
                                

                              Hmmm, I'd use a different less complex approach myself as I've indicated in earlier posts in this thread but to answer your questions...

                               

                              "Do I need to add the checkbox as a new field in a table? It is possibile to have an "user interface" checkbox (something that it's in the layout, but not in the database and that I can control from a script?"

                              You'll need a field. In some cases you can use a global field as a "user interface" object, but in this case you need field that can hold the value for each selection.

                               

                              "Can I do the same script in SQL? The script is just 3 SQL queries (and I'm more familiar with SQL than with filemaker scripting)"

                              Not as far as I know.