6 Replies Latest reply on Feb 16, 2010 2:02 PM by natwroy

    Portal information display question



      Portal information display question


      I am trying to get a layout that I call "Customers pricelist" to show a listing of all the tests I offer and allow me to enter the price for every test that they can take. Every customer is going to order every test multiple times during the year. At the beginning of each year they renegotiate their test prices based on how much they ordered the year before.  I need this "Customer" layout in the database that lists all 50 possible tests and 50 spots for prices so that I can easily update all of the prices that customer is going to pay for the next year. Then monthly I will invoice them for the tests they have had taken that month and of course I need to be able to go back through the invoices from last year and not have had the price change on the previous years invoices even though I update the price for the test every year. 


      Right now I have four tables CUSTOMERS, INVOICES, TESTS, and TEST_PRICES.  CUSTOMERS is linked to INVOICES as a one to many. INVOICES is linked to TEST_PRICES as a one to many. TEST_PRICES is linked to TESTS as a many to one.


      I have the field test_name from the TESTS table and the field price from the TEST_PRICES table in a portal on the "Customers pricelist" layout, but a list of test_name isn't showing up. 


      Can someone please tell me how to set this up, and if any of my table relationships are wrong please tell me. I don't have any production information in this yet so I can change anything. I am using Filemaker 10 on a mac and I'm a newbie. Thanks. 

        • 1. Re: Portal information display question

          One way would be to simply create the first invoice for the year that itemizes each item purchased in a line items table displayed in a portal.


          Then for each subsequent invoice of the year, simply duplicate both the invoice and line items records. This takes a script and you have to loop through your line item records to both duplicate them and then change their invoice numbers to match that of the new invoice, but it can be done fairly simply and you have the added advantage of being able to support a customer renegotiating their contract mid-year if you choose to permit that.

          • 2. Re: Portal information display question
               I thought it would be more simple than that. Why doesn't it work to just have a portal with TESTS::test_name TEST_PRICES::price show all their records on the "Customers pricelist" layout. I know that it doesn't work but I don't understand why. There isn't a simple way to do this?
            • 3. Re: Portal information display question

              It can, if you have a different set of test_price records for every customer, for each year.


              The draw back is that all your invoices for the year will be the same. Any changes to your test_price records will change all your past invoices for the same year. In most businesses, that's a bad idea because they can't guarantee that the invoices will be exactly the same each month. If you business is an exception to that and you are sure you will always invoice a customer exactly the same way each year, then yes, you can set up a table like this, but make sure you include both a customer ID and an effective date field so that you can create a new set of these records every year.


              Setting up a "duplicate current invoice" script isn't all that complicated.

              • 4. Re: Portal information display question
                   How would I go about setting up that script then? I don't have any experience with filemaker scripting yet.
                • 5. Re: Portal information display question

                  Adapting from an earlier thread on the same subject:


                  #Capture the number of the next new invoice and save it in a variable

                  Set Variable [$NewInvoice, GetNextSerialValue ( get(fileName) ; YourTable::YourInvoiceID)]

                  #Pull up the portal's records in a found set on a layout that refers to the portal records

                  Go To Related Record [Show Only Related Records ; From Table "YourPortalTable"; Using Layout "Your Layout" (YourPortalTable)]


                      #Duplicate each portal record and give it the correct ID number to link to the new invoice

                      Duplicate Record/Request

                      Set Field [PortalTable::InvoiceID, $NewInvoice]

                      Omit Record

                      Go To Record/Request/Page [First]

                      Omit Record

                      Exit Loop If [Get(foundcount) = 0]

                  End Loop

                  Go To layout [original layout]

                  #Duplicate the Invoice Record

                  Duplicate Record/Request


                  You'll need to substitute your names for the object names given in this script. "PortalTable", for example, would be replaced by the name of your line items table.

                  • 6. Re: Portal information display question
                       Awesome! I'll give this a shot and let you know how it goes.