4 Replies Latest reply on Mar 28, 2012 9:10 AM by BorisBalkan

    Price connected to date

    BorisBalkan

      Title

      Price connected to date

      Post

      Hello,

       

      I'm trying to create a small price calculator for a b&b.

      There is a Pricelist connected by dates (i.e. from March 6 to 15 $20 and from March 16 to March 30 $25)

       

      What I am trying to do is a calculation like:

      From March 10 to March 20 = 5 days at $20 and 4 days at $25

       

      Thanks in advance for any suggestion

        • 1. Re: Price connected to date
          philmodjunk

          What fields have you defined in the PriceList table?

          How have you designed the other tables where you need to calculate a customer's bill?

          Does it resemble this structure?

          Customers----<Invoice----<LineItems>--------ProductsAndServices

          • 2. Re: Price connected to date
            BorisBalkan

            Actually the Price List is designed:

            FIELD 1: From (date)

            FIELD 2: To (date)

            FIELD 3: Price

            Probably giving a price for EVERY single day will work better? But if it's so I must find a way to add/replace prices easily than manage day-prices one-by-one 

             

            The working table is:

             

            FIELD 1: From (date)

            FIELD 2: To (date)

            FIELD 3: Rooms (number of rooms)

            FIELD 4: TOTAL Price (calculation) 

             

            (PS: Whoops, I've wrongly chosen my post as "Best Answer" --- I am a Filemaker newbie)

            Thanks!!

            • 3. Re: Price connected to date
              philmodjunk

              Giving a price for every single day is a valid approach here and one that can be managed fairly cleanly with scripts so that you specify a from, a to and a price and the script then creates the records in your pricing table. But I don't think we need that unless you encounter unacceptable delays computing a guest's bill.

              What's of more concern is that it appears that each date of a guest's stay might be a different charge. They might, for example, stay at your B & B starting on March 15 and leaving on March 17--resulting in a charge of $20 for the first night and $25 for the second.

              If that's an accurate depiction of your business methods, I recommend setting up a related table with one record for each night of the guest's visit.

              The relationships you might use would look like this:

              Invoices---<Nights>----NightlyRates

              Invoices::InvoiceID = Nights::InvoiceID

              Nights::Date > Rates::From AND
              Nights::Date < Rates::To

              Nights::Date, Rates::From and Rates::To should all be fields of type date.

              A RoomRate field in Nights can use a looked up value auto-enter field option to look up the nightly room rate.

              A cTotalRoomRate field in Invoices (where you'd have the From and To fields of the guest's stay), would be defined as a calculation field:

              Sum ( Nights::RoomRate )

              Here's a script that could generate the records in Nights once you have entered a From and To date in INvoices:

              Set variable [$DateFrom ; Value: Invoices::From ]
              Set variable [$DateTo ; Value: Invoices::To ]
              Set Variable [$InvoiceId ; value: Invoices::InvoiceID]
              Freeze Window
              If [Nights::date //Nights records have already been created for this invoice]
                 Go To Related Record [Show only related records; From table: Nights; Using layout: "Nights" (Nights)]
                 Delete All Records [No dialog]
              Else
                 go to Layout [ Nights ]
              End If
              Loop
                 Exit Loop If [$DateFrom > $DateTo ]
                 New Record/Request
                 Set Field [Nights::InvoiceID ; $InvoiceID ]
                 Set Field [Nights::Date ; $DateFrom ]
                 Set Variable [$DateFrom ; Value: $DateFrom + 1 ]
              End Loop
              Go to layout [original layout]

              Note 1: The If block just before the loop deletes any Nights records that already exist for this Invoice record. This is done so that if you need to change a From or To date, you can run the script again to get an updated set of related records without any duplicated dates or dates that no longer fall within the updated date range.

              Note 2: You can probably set up a script trigger to run this script automatically. If you do, insert another If step that checks the Invoices::From and Invoices::To fields to make sure that they are not empty at the start of this script.

              Note 3: If you decide to build your price list table with one record for each day instead of From To ranges, this script can serve as a good model for how you might manage these records so that you only need specify a rate and date range and yet get your individual date records.

              • 4. Re: Price connected to date
                BorisBalkan

                Wow! Thanks for the detailed reply!

                 

                I work on it and then I will post the results!

                 

                Many, many Thanks again!