1 2 3 4 Previous Next 50 Replies Latest reply on Aug 15, 2012 9:02 AM by philmodjunk

    Help with Invoice



      Help with Invoice


      Hello to All, (Phil)

      I'm trying to create an invoice for trips that are completed.

      What I would like to do is

      to use table view for filter and settle the clients that I want to bill. Once these trips are settle I can create an invoice.

      Or any suggestion on how to invoice the complete trips?

      Can I use a check box in table view?



        • 1. Re: Help with Invoice

          Hello Phil,

          Looking at the invoices file in the starter solution, I think I can use it for my invoice. what I may have to do is to create a relationship between Invoice Data and Reservations instead of Invoice Data and Products.

          Now which field should I use to link them __pk_ReservationID or Trip_no?

          • 2. Re: Help with Invoice

            Each invoice is to bill a single customer for multiple reservations?

            If so, think of reservations as your LineItems table.

            You may find this demo file helpful: http://fmforums.com/forum/showpost.php?post/309136/

            In your case, the table occurrences might be:


            The key difference will be the need to select Reservations for your invoice so that all reservations are billed on an invoice, but never the same reservation on more than one invoice. You may need to modify my suggestion to get it to work with your business procedures, but it's possible for a portal to Reservations to list all Reservations for a given patient that have not yet been billed on an invoice.

            • 3. Re: Help with Invoice

              This is exactly what I want to do, I have a payment status in my reservation table (Paid, Unpaid), but how can I pull the unpaid trips to create the invoice? There a "+Add Line Item" button in the invoice layout, I'm thinking that's where I can  select unpaid trips and add them to the invoice. Is it possible to have a popup list of Paid / Unpaid trips


              Patients----<Invoices-----<Reservations   ......Which field should I used for Invoices----------<Reservations ?

              • 4. Re: Help with Invoice

                Invoices should be a new table in your database so that you can create a new record each time you need to bill a patient for services rendered. This can be automated or manual.

                For starters, you can set up these relationships:

                Patients::PatientID = Invoices::PatientID

                Invoices::PatientID = PatientReservations::PatientID

                You can put a portal to PatientReservations, but apply a filter to filter out those that have been invoiced, cancellations, etc--any reservations for this patient that you don't want on the invoice.

                Then, either one reservation at a time or via a script that does this with all the reservations shown in the portal, assign them to the current invoice by putting the current Invoice's InvoiceID into an InvoiceId field in Reservations. You can set up a button in the portal row with a script for selecting one reservation at a time or you can put an "add all" button on your invoice layout that assigns all of them to your invoice.

                This will make possible this relationship:

                Invoices::InvoiceID = InvoicedReservations::InvoiceID

                • 5. Re: Help with Invoice


                  I have it setup like this

                  Patients::__pk_PatientID = Invoices::_fk_PatientID

                  Invoices::_fk_PatientID = PatientReservations::_fk_PatientID


                  I already create an Invoice, Invoice Data tables

                  Also the Staters  have these relationships:

                  UNPAID INVOICES

                  Unpaid Invoices::Payment Status = Patients::UNPAID MATCHED FIELD (?)

                  AND Unpaid Invoices::__pk_InvoiceID = Patients::__pk_PatientID

                  PAID INVOICES

                  paid Invoices::Payment Status = Patients::PAID MATCHED FIELD (?)

                  AND paid Invoices::__pk_InvoiceID = Patients::__pk_PatientID


                  Is that something that I need too?


                  If I'm creating a portal in the reservaton layout then I should have the data coming from Reservation Self. Right?




                  • 6. Re: Help with Invoice

                    How would you use those relationships?

                    You can do it that way, but you can also perform a find for paid or unpaid invoices and you can also use a portal with a filter that limits the invoice records listed to paid or unpaid.

                    If I'm creating a portal in the reservaton layout then I should have the data coming from Reservation Self. Right?

                    For what pupose?

                    For invoicing, you need to be an a layout based on Invoices.

                    • 7. Re: Help with Invoice
                      You had suggested earlier that I can use a portal " You can put a portal to PatientReservations, but apply a filter to filter out those that have been invoiced..." What I need, when I click on the line item button, a list of unpaid trips should popup for me to add them in the invoice.
                      • 8. Re: Help with Invoice

                        That portal would be on the invoices layout and the portal IS the list of uninvoiced (not unpaid) reservation records.

                        If that's all you did, there'd be a problem as any invoice for the same patient would list the same reservation records and the list would change as new reservations are made. To permanently link a set of reservation records could then be done one record at a time or all the records listed in the portal could be updated with the current InvoiceID to link them to the current invoice.

                        Freeze Window
                        Set Variable [$InvoiceID ; value: Invoices::InvoiceID ]
                        Go To Related Record [Show only related records; From table: Reservations; Using layout: "Reservations" (Reservations)]
                        #This list will include records kept out of portal with a portal filter constrain found set to omit the records that were filtered out
                        Enter Find mode []
                        Set Field [Reservations::InvoiceID ; "=" ] // limt found set to only uninvoiced reservation records
                        #You may need to specify additional criteria to omit other records that should not be invoiced such as cancellations.
                        Set Error Capture [on]
                        Constrain Found Set []
                        If [ Get ( FoundCount ) ]
                          Replace Field Contents [no dialog; Reservations::InvoiceID ; $InvoiceID ]
                        End If
                        Go to Layout [original Layout]

                        • 9. Re: Help with Invoice

                          Hi Phil,

                          I didn't create any relationship yet between Invoices and Reservations. Which 2 fields that I need to use to create that relationship?

                          In creating the if statement I get an error message. Is it because the relationship between Invoices and Reservations has not created yet?

                          By me doing this script, will that work without the portal being in the reservation layout?

                          • 10. Re: Help with Invoice

                            I spelled out the relationships needed in an earlier post. You need those first before doing anything more. keep in mind that you can use a separate set of table occurrences for this so that it does not make the rest of your relationship graph too complex.

                            • 11. Re: Help with Invoice

                              This is the way I have my relationship and the script is still not working.

                              • 12. Re: Help with Invoice

                                You have the first relationship:

                                Invoices::_fk_ClientID = Reservations 2::_fk_ClientID

                                but not the second:

                                Invoices::_pk_InvoiceID = InvoicedRESERVATIONS::_fk_InvoiceID

                                Your script would take the list of records produced via relationship to Reservations 2 and a constrain found set to assign InvoiceIDs so that a portal using the second relationship would display them.

                                • 13. Re: Help with Invoice

                                  Hi Phil,

                                  I don't have a table InvoicedRESERVATIONS, should I create an occurrence table from which table?

                                  The portal will be in the Reservations layout showing records from Reservations 2, right?

                                  • 14. Re: Help with Invoice

                                    Note the part of the name that is in all caps. This is a frequently used naming convention. The all caps part of the name identifies the data source table of which this is an occurrence. Thus, this should also be an occurrence of Reservations.

                                    To repeat, the portals here will be on the invoices layout. You may, in fact, have TWO portals, one to Reservations 2 and one to invoicedRESERVATIONS. Running your script that assigns an Invoice ID to the reservations records will make them disappear from the portal to Reservations 2 and appear in the portal to invoicedRESERVATIONS.

                                    1 2 3 4 Previous Next