8 Replies Latest reply on Sep 12, 2012 10:12 AM by philmodjunk

    Finding Previous Invoice ID on starter solution

    JasonPenner

      Title

      Finding Previous Invoice ID on starter solution

      Post

      I've modified the invoice starter solution but I'd like to have a field in the Customer table that calculates their last invoiceID. So say Customer 3 has two invoices, #32 and #2 when I add a new invoice the "Previous Field" is calculated to be #32. I was playing with GetNthRecord but I can't filter it per customer. I'm trying to do this so I can have a portal on the invoice page that lists the previous purchase, using the filter on the portal.

       

      Thanks

      Jason

        • 1. Re: Finding Previous Invoice ID on starter solution
          philmodjunk

          GetNthRecord could work, but you'll need to add a relationship that matches invoices to each other by customer.

          You can add a new occurrence of the invoice table and link it to the original occurrence by CustomerID (or whatever it's called in the starter solution).

          Then a portal to this new occurrence would list all invoices by the same customer as the current customer selected for your invoice.

          IF you'd like a more detailed description of this, please tell me which version of FileMaker you have. THe FileMaker 12 starter solutions are quite different from the FileMaker 11 starter solutions so I'd need to know which one you are using.

          • 2. Re: Finding Previous Invoice ID on starter solution
            JasonPenner

            Using FM12... and I should clarify that I'm looking to have the line-items in a portal. It makes life way easier for my staff if they can see previous orders when intputing a new one.

             

            Jason

            • 3. Re: Finding Previous Invoice ID on starter solution
              philmodjunk

              The line items from the previous invoice?

              No problem.

              In Manage | Database | relationships, make a new table occurrence of Invoices by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be PreviousInvoices.

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              Repeat this process to make a new occurrence of Invoice Data (what they called LineItems in this soluiton). Name it PrevInvoiceData.

              Add them to your relationships like this:

              Invoices----<PreviousInvoices-----<PrevInvoiceData

              Invoices::CUSTOMER ID MATCH FIELD = PreviousInvoices::CUSTOMER ID MATCH FIELD AND
              Invoices::Invoice ID ≠ PreviousInvoices::Invoice ID

              PreviousInvoices::Invoice ID = PrevInvoiceData::INVOICE ID MATCH FIELD

              Double click the relationship line between Invoices and PreviousInvoices. Click the check box to sort the records in PreviousInvoices by Invoice ID in descending order. This will make the first related record in PreviousInvoices the most recent previous invoice with the same customer ID.

              Put a portal to PrevInvoiceData on your layout. Give the portal this portal filter to omit all but the line items from the most recent previous invoice:

              PreviousInvoices::Invoice ID = PrevInvoiceData::INVOICE ID MATCH FIELD

              • 4. Re: Finding Previous Invoice ID on starter solution
                JasonPenner

                     What am I missing here... right now the "Previous" portal is showing the first line item of the current invoice, over 23,000 times. Please see attached photo for my relationships

                • 5. Re: Finding Previous Invoice ID on starter solution
                  philmodjunk

                       You don't have these relationships:

                       WorkOuts----<PreviousWorkOuts-----<PreviousWorkoutLineItems

                       WorkOuts::Customer ID= PreviousWorkOuts::Customer ID AND
                       WorkOuts::Workout ID ≠ PreviousWorkOuts::Workout ID

                       PreviousWorkOuts::Workout ID = PreviousWorkoutLineItems::Workout ID

                       WorkOuts should link to PreviousWorkouts, not Previous Workout Line Items and then PreviousWorkouts links to Previous Workout Line Items

                  • 6. Re: Finding Previous Invoice ID on starter solution
                    JasonPenner

                         OK after writing I have made some adjustments and got the line items from a previous workout to show, but it always chooses the first record. I can get it to choose the workout immediatly before. Here's what my relationship table looks like.

                    • 7. Re: Finding Previous Invoice ID on starter solution
                      JasonPenner

                           I believe my issue is now getting the next "InvoiceID" or Workout ID for the previous workout. If I'm on the first record of a client I get the previous lineitems for the 2nd record and the 2nd record on I get the lineitems for the first record. Could it be a sorting issue?

                      • 8. Re: Finding Previous Invoice ID on starter solution
                        philmodjunk

                             The relationship will match to all previous records. You could use sorting and a portal filter to limit the line items listed to only those from the previous invoice for the same customer.

                             Double click the line between Previous workouts and Workouts. Select the sort option for Previous Workouts and specify that the records be sorted by Workout ID in descending order.

                             Then use this portal filter on the portal to Previous Workout Line Items:

                             Previous Workouts::Workout ID = Previous Workout Line Items::kf_Workout ID

                             (This should work to limit the line items to just the most recent previous workouts record because the reference to Previous Workouts::Workout ID should evaluate from the context of Workouts.)