11 Replies Latest reply on Jul 26, 2013 10:10 AM by Tusquittee

    Set report to generate invoice

    Tusquittee

      Title

      Set report to generate invoice

      Post

           Good morning experts, 

           I've been working little by little on a registration database for students in an academic program.  The students have registrations which function like invoices and they make payments to cover those invoices.  So far so good... 

           The students are also in specific conferences, Students::Annual Conference.  And thanks to the great help on here I even have a multi-tiered approach where the user first selects jurisdiction, then conference, and then even district.  So, I'm really happy with the way things are working.

           Then I realized I needed to be able to send invoices not just to the students, but also to the annual conferences that they are in.  The student pays a portion of their registration fees and the conference pays another portion.  Further, the conference bill should be a TOTAL bill for all students from that particular conference during a specific session.

           In order to accomplish this, and deal w/ the arrangement that the conference doesn't pay the same amount for each student... I created a sub-summary report that works well.  It generates a report for whichever conference and session are chosen and populates it with the names, classes, and fees associated with each student in that conference.  

           I thought this would work, but am now finding out that some conferences pay 50% of their portion and are billed two months later for the remaining balance... some conferences pay in installments.. and some wait so long to pay that they are accessed late fees.  Now I figure I need some way to accomplish billing for annual conferences instead of simply generating a bill statement.  

           My first thought was to create a field that would allow the user to exit preview and input payment information if it had already been made for that invoice.  (This would really not be ideal, but might work as a temporary solution..) I was thinking something similiar to the Invoice Starter Solution where the header field is a text auto enter calculation that invites the user to exit preview and enter some info.  When I tried this, also w/ a text field set up as auto enter calculation, after i exted preview I could ineed enter information, but when I go back to preview or to print or to save, the information isnt' there....Not sure why?

           My second thought was... is it possible to use a report to generate an invoice for each specific  conference? 

      db.jpg

        • 1. Re: Set report to generate invoice
          philmodjunk

               It's not clear what you want to do with that header field and there are a number of possible issues why data entered into it disappears. It may be that the field is defined in the wrong table or added from the wrong table occurrence.

               It seems like you actually have two related issues:

               1) Managing payments on these invoices

               2) Managing "repeat billing" for unpaid invoices

               Does that sound correct to you?

          • 2. Re: Set report to generate invoice
            Tusquittee

            smiley You're right Phil.. the header field was defined in the wrong table but wasn't the best solution anyway since, as you point out, I really need to both manage the payment and the repeat billing.  Sounds correct to me...

                 I guess i need to think about this differently.  I was just generating a report.. not really creating an invoice... but, I noticed in one of your other posts you talk about scripting an invoice number at the time of printing.. I think I need to create a table to hold all of the generated invoices and then put a portal to that table on the conference layout.  In that way, I think I could get started? Does this sound right?... 

                  

            • 3. Re: Set report to generate invoice
              Tusquittee

                   Okay, I got ahead of myself in that last post.  Here's where my difficulty comes in.. or at least I think so.  

                   I don't currently have a table for conference invoices.  The conference invoice is actually a subsummary report based on the RegData table and contains records from each of the specific class registrations that were made by students from that specific annual conference during a speciic session.  

                   Do I need to 

                   a.  create a new table called conference invoices linked to the conference table with a one to many relationship.  Every conference can have many invoices.

                   b.  have the subsummary report somehow generate an invoice in this new table? OR.. save it in a container field in that table? (surely this can't be right?)....OR... have the script that generates the subsummary report also set some fields in my conference invoice table.  This way there's a new record with an actual invoice in the conf. invoice table...

                   This final way of using the script to preview the report and then set fields in my conference invoice table seems like the best bet...

                   I'm still wrapping my head around this.. am I off in the wrong direction again?

                    

              • 4. Re: Set report to generate invoice
                Tusquittee

                     I think I have the new relationship structure worked out.. but I'm still having difficulties with the script to set certain fields.  The issue is that the field I'm trying to set, Annual Conference Invoices::Conference Fees, is a number field... and the information for it comes from a summary field, Reg Data::Fees Total by Conference.  I've tried all sorts of configurations including...

                GetSummary.. which didn't work because I don't have a break field...

                GetValue.. and even, get field.....

                Is it possible to set a field with numerical data from a summary field? How does one refer to the numerical value within a summary field in a calculation?  Thanks for any assistance....

                      

                      

                      

                Print Annual Conference Report Copy

                Set Error Capture [ On ]
                Go to Related Record [ From table: “Reg Data”; Using layout: “Annual Conference Page One Copy” (Reg Data) ]

                [ Show only related records; Match found set; New window ]

                Sort Records [ Keep records in sorted order; Specified Sort Order: Students::Annual Conference; ascending Registrations::Session Name; ascending
                          Students::Contact Name; ascending ]
                          [ Restore; No dialog ]

                Set Variable [ $annualconfinvoices; Value:Registrations::Academic Year Name & " " & Registrations::Session Name & " " & Students:: Annual Conference & " Invoices.pdf" ]

                Save Records as PDF [ File Name: “$annualconfinvoices”; Records being browsed ]
                          [
                Document - Compatibility: Acrobat 7 and later ]
                          [
                Pages - Number Pages From: 1; Include: All pages ]
                          [
                Security - Printing: High Resolution; Editing: Any except extracting pages; Enable copying; Enable Screen Reader ] [ Initial View - Show: Pages Panel and Page; Page Layout: Single Page; Magnification: 100% ]

                [ Restore; No dialog ]

                New Window [ Top: 200; Left: 155; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]

                Go to Layout [ “Conferences” (LPR Table 2) ]

                Perform Script [ “Add Invoice from Conference Panel” ]

                Set Field [ Annual Conference Invoices::Conference Fees; GetSummary ( Reg Data::Fees Total by Conference ;Reg Data::Annual Conference) ]

                Commit Records/Requests

                [ Skip data entry validation; No dialog ] 

                      

                • 5. Re: Set report to generate invoice
                  philmodjunk

                       If you are trying to get the same total from a summary field as you see in a sub summary layout part, then GetSummary() is the function to use and your "break field" should be the same field that you specify as the "when sorted by" field in the sub summary part's setup.

                       If that's not the issue: (And calculations inside of script steps such as set field or set variable evaluate by the same rules specified here for calculation fields defined in the current layout's table.)

                       A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

                  Summary field is referenced on a layout based on the table in which it was defined:

                  A group within a FoundSet

                       If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

                       In a calculation, you can use the getSummary function to access the same group based sub total.

                  All the records in a FoundSet

                       If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

                       If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

                  Summary field is referenced on a layout based on a table related to the table in which it was defined:

                  Not in a Filtered Portal

                       If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

                       Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

                  In a Filtered Portal (FileMaker 11 and newer only)

                       If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

                       This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

                       This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

                       Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.

                  • 6. Re: Set report to generate invoice
                    Tusquittee

                         Thanks for every comment Phil... I really do appreciate all of your help.  I read and search ... so sometimes it takes me awhile.  I'm going to try the GetSummary now that you've explained the break field... and i'll see what happens...

                         I've duplicated the same relationship structure and layouts I was using for student payments and that seems to work well except for getting the payments to show up on the invoices layout.  I'm using a portal from Line Items 2 on an Annual Conferences layout and nothing is happening.  

                         In my previous setup with student payments... I could "see" every students payments by looking at a portal on registrations detail layout that came from Line Items.... I've probably just messed up a field somewhere along the way... Does the relationship graph at least look like I'm going in the right direction?

                    • 7. Re: Set report to generate invoice
                      philmodjunk

                           And which table occurrence of those shown is the one used for your invoices layout?

                           Annual COnference Invoices or Annual Conference Invoices 2?

                           A portal to LineITems 2 placed on a layout based on Annual Conference Invoices should allow you to list the Payment Amount Field from Line ITems 2.

                           If you ever get payments that must be applied to more than one invoice--one payment pays off two conferences in order to get "caught up"), then you really need two payment fields, one in Payments that records the full amount of the payment received and one in LIne Items 2 that records the portion that is to be applied to a specific conference Invoice.

                      • 8. Re: Set report to generate invoice
                        Tusquittee

                             Hi again Phil... sure do appreciate all your help... 

                             1.  As regards the Annual Conf. layout:

                                I took a snapshot of the database to show you that I did indeed have a portal to LineItems2 placed on a layout based on Annual Conference Invoices.  It was then that I noticed the portal was filtered...surprise... oh, my my my... 

                             2.  Back to my friend the summary field.....

                             So, the summary field, Reg Data::Fees total by Conference, is on a layout based on the Reg Data table.  It's a summary of the fees I was asking the Case Statement questions about.  Those course fees are in the body of the layout and the Summary field is located in the sub summary by Students::Annual Conference part.  

                             If I understood what you said about the break field, GetSummary ( Fees Total by Conference ; Students::Annual Conference ).. this should return the numberical value of the summary field?

                             To test this I created a calculation field on the same layout and I get nothing. (Calculation result is set to number)

                             So, should a GetSummary calculation field work on the same layout? (I tried moving the field from part to part.. .. and nothing showed up).  

                              

                        • 9. Re: Set report to generate invoice
                          Tusquittee

                               I'm still stuck on the set field problem using the summary field.. and I think the problem lies in my script.

                               I gave up working on the GetSummary function just to see if I could get anything to go into the designated field when I used set field in the script.  and no luck.... Here's the script I'm using.

                                

                          Set Error Capture [ On ]
                               Go to Related Record [ From table: “Reg Data”; Using layout: “Annual Conference Page One Copy” (Reg Data) ]

                          [ Show only related records; Match found set; New window ]

                          Sort Records [ Keep records in sorted order; Specified Sort Order: Students::Annual Conference; ascending Registrations::Session Name; ascending
                               Students::Contact Name; ascending ]
                               [ Restore; No dialog ]

                          Set Variable [ $annualconference; Value:Students::Annual Conference ]
                               Set Variable [ $annualconfinvoices; Value:Registrations::Academic Year Name & " " & Registrations::Session Name & " " & Students::

                          Annual Conference & " Invoices.pdf" ]

                          Save Records as PDF [ File Name: “$annualconfinvoices”; Records being browsed ]
                               [ Document - Compatibility: Acrobat 7 and later ]
                               [ Pages - Number Pages From: 1; Include: All pages ]
                               [ Security - Printing: High Resolution; Editing: Any except extracting pages; Enable copying; Enable Screen Reader ] [ Initial View - Show: Page Only; Page Layout: Single Page; Magnification: Fit Width ]

                          [ Restore; No dialog ]

                          New Window [ Top: 100; Left: 100; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]

                          Go to Layout [ “Conferences” (LPR Table 2) ]
                               Perform Find [ Specified Find Requests: Find Records; Criteria: LPR Table 2::Conference Name: “="$annualconference"” ]

                          [ Restore ]

                          Enter Browse Mode
                               Perform Script
                          [ “Add Invoice from Conference Panel” ]

                          //add invoice from conf. panel script.

                          Set Variable [ $$CURRENT_CONFERENCE_ID; Value:LPR Table 2::CONFERENCE ID ]

                          Go to Layout [ “LPR Card Detail” (Annual Conference Invoices) ]
                               New Record/Request
                               Set Field
                          [ Annual Conference Invoices::Annual Conference ID; $ $CURRENT_CONFERENCE_ID ]

                               //end of add invoice from conf. panel script

                          Insert File [ Annual Conference Invoices::Invoice PDF; “$annualconfinvoices” ] [ Storage method: Insert ]
                               [ Display content ]
                               [ Compression: Never compress ]

                          Set Field [ Annual Conference Invoices::Conference Fees; Reg Data::Fees Total by Conference Copy ] 

                          Commit Records/Requests

                          [ Skip data entry validation; No dialog ]
                               Close Window [ Name: "Annual Conference"; Current file ]

                                

                               I've tried setting the field from the actual summary field... 

                               I've tried setting the field using GetSummary(Reg Data::Fees Total by Conference); Students::Annual Conference

                               I've tried setting the field using GetSummary(Reg Data::Fees Total by Conference); Reg Data::Fees Total by Conference

                               I even, in the above script example... created a new field which is just a number.. auto entered calculation of 100.. and tried to set that field into the Conference Fees field...

                               What am I missing about the Set Field command? Interestingly enough, the insert file command just above it works great...

                                
                          • 10. Re: Set report to generate invoice
                            philmodjunk

                                 In your script, your set field step refers to the contents of Fees Total by Conference Copy from a layout based on a table occurrence named: Annual Conference Invoices. In that context the value entered into the field will be determined by the relationship linking the current record of Annual Conference Invoices to a set of related records in Reg Data. If you refer to a summary field in Reg Data like this, it will return a value based on the set of related records in Reg Data that link to Annual Conference Invoices.

                            Since you are getting no value entered, I would guess that the current record in Annual Conference Invoices does not link to any records in Reg Data.

                            GetSummary cannot be used in this context as it can only be used from the context of a layout based on the table where the summary field is defined as it interacts with your current found set to compute a value. But you shouldn't need GetSummary in this context if you can define a relationship that matches to the correct records in Reg Data from which to compute your total via the summary field.

                                 More about Get Summary:

                                 Just as a sub summary layout part disappears if you have not sorted the records by the break field, getSummary returns nil if the current found set of records is not sorted by the break field specified as a parameter in the GetSummary function call.

                                 Since GetSummary uses the summary field to compute a sub total based on your current found set of records and how they are sorted, you will get different values returned by it depending on what records are present in your found set, exactly how they are sorted, and (when used in Set Field to capture the sub total), what record is the current record.

                                 If your sort that includes the break field groups the records so that the first 5 records are in the same group (have the same value in the break field), then set field [YourTable::Field ; GetSummary (SummaryFIeld ; BreakField ) ] will put exactly the same value into the field from any of the first 5 records. Use go to record to move to the 6th record, and getSummary returns a different sub total, the subtotal computed from the next group.

                                 But this has to be done from the context of the field where the summary field is defined, not from the context of a related table.

                            • 11. Re: Set report to generate invoice
                              Tusquittee

                                   Thanks... my problem is, wwell, at least one of the largest problems, lol, is that the Annual Conf. Invoices aren't related to Reg Data...Let me play around with this a bit more.