1 2 Previous Next 20 Replies Latest reply on Mar 17, 2014 2:22 PM by philmodjunk

    Calulation for date range..assigning custom values based on variables

    jl2012

      Title

      Calulation for date range..assigning custom values based on variables

      Post

           Hello All,

           So I have been thinking on this one for some time and thought I would consult the forum brain.  I have a rental database that I would like to automate.  I have different prices for different days of the week as well as different times of the year.  Here is a list of the prices:

           November-February & August - Sunday-Thursday = $160.00 per night

           November-February & August - Friday-Saturday = $195.00 per night

           November-February & August - Weekly Stays = $1150.00 per week

           March-October - Sunday-Thursday = $190.00 per night

           March-October - Friday-Saturday = $225.00 per night

           March-October - Weekly Stays = $1350.00 per week

           In my database I have a DateStart field and a DateEnd field.  I would love to create a calculation that would evaluate the date range and assign the proper prices to each night stayed.  For example:

           DateStart = 02/13/2014

           DateEnd = 02/18/2014

           Evaluated Field should read:

           3 Nights at $160.00 ($480.00)

           2 Nights at $195.00 ($390.00)

           Is this possible?  I'm sure it is but it is way outside of my league.  Any help on this would be greatly appreciated!

            

        • 1. Re: Calulation for date range..assigning custom values based on variables
          philmodjunk

               There are two basic approaches that can work:

               1) Use a script to calculate the cost. The script can access records in your pricing table and sum up the total charge for the date range needed in a loop.

               2) Use a script to generate records in yet another table where the script creates one record for each date from dateStart to DateEnd. These records link to your pricing table and look up the current rate given the date range each individual date falls in. the sum of these nightly charges will then equal your total charges for the specified date range.

               Both of these aren't terribly different methods, they are just slightly different variations of the same basic method of looping from DateStart to DateEnd to compute a total rental charge.

          • 2. Re: Calulation for date range..assigning custom values based on variables
            jl2012

                 So forgive my ignorance here...not really sure where to begin.  You mentioned creating a table with my prices and creating a script from my rental database to said table to access the prices for the date range?  Would I just create a table with a price field and make a record for each price I have?  Not exactly sure how to get a script to pull up that information. 

                 The second option made more sense, as I would like to get a price for each day based on the date range first entered.  By being able to separate out the date range into separate dates and then assigning the prices to them, seems the most logical way to do it.  I am however, still at a loss on how to make this happen.  Any additional help would be greatly appreciated.

            • 3. Re: Calulation for date range..assigning custom values based on variables
              philmodjunk

                   Your basic tables and relationships might look like this:

                   RentalInvoices----<RentalDates>------RentalRates

                   RentalInvoices::__pkRentalInvoiceID = RentalDates::_fkRentalInvoiceID

                   RentalDates::Date > RentalRates::StartDate AND
                   RentalDates::Date < RentalRates::EndDate AND
                   RentalDates::Type = RentalRates::Type

                   The Type fields will store "Nightly" or "Weekly" to distinguish between nightly and weekly rentals.

                   Using your example data, you'd create a record in RentalRates with a Start date of 11/1/2014 and an end date of 2/28/2015, a type of "Nightly" and a rate of $160. A second nightly rental rate record with the same rate would have start/end dates of 8/1/2014 and 8/31/2014 for your August rates.

                   A looping script would use start and end dates in RentalInvoices to create the needed records in RentalDates.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   PS. I am posting what I think of as "wedge" responses. I start with a very general outline of my suggested answer--the tip of the wedge. With each subsequent response, I supply more detail--the next and wider section of the wedge, until the info I have provided is sufficient for someone with your level of expertise to be able to make the suggested solution work.

                   So keep posting follow up questions until you have sufficient understanding to get things working for you!

              • 4. Re: Calulation for date range..assigning custom values based on variables
                jl2012

                     I thought I saw where you were going with this, but I got lost with the looping script.  I have created the appropriate tables, fields and relationships you have stated above.  One thing...when entering an auto-enter serial, it just creates a 1, should I make it more specific or does that not matter in this instance?

                     So I am assuming the looping script generates the individual records for each day in the RentalDate table and assigns the appropriate rate based on the relationship with the RentalRate table.  Just never done a looping script before.  I think I need another wedge! :)

                • 5. Re: Calulation for date range..assigning custom values based on variables
                  philmodjunk
                       

                            when entering an auto-enter serial, it just creates a 1,

                       It will enters a 1 in the first record, a 2 in the next and a 3 in the one after that... Simple is what you want for your primary keys. A simple sequential numbering system uniquely identifies each record and that's all that you should ever need for that field. There's an alternative approach that uses Get ( UUID ), but unless you plan on synching multiple destributed copies of your database, it's simpler to use an auto-entered serial number as your primary key.

                       

                            So I am assuming the looping script generates the individual records for each day in the RentalDate table and assigns the appropriate rate based on the relationship with the RentalRate table.

                       Close. It creates the records with those dates and linked to the current RentalInvoices records and linked to the selected RentalRates record. But the rate for each day happens as an auto-enter field option on a rate field in RentalDate without any scripting needed.

                       If [Not Isempty ( RentalINvoice::StartDate ) and RentalInvoice::StartDate < RentalInvoice::EndDate ]
                          Set Variable [$Date ; Value: RentalInvoice::StartDate ]
                          Set Variable [$InvoiceID ; Value: RentalInvoice::__pkRentalINvoiceID ]
                          Freeze Window
                          Go To Layout [ "RentalDates" (RentalDates) ]
                          Loop
                             New Record/Request
                             Set Field [RentalDates::_fkRentalInvoiceID ; $InvoiceID ]
                             Set Field [ RentalDates::Date ; $Date ]
                             Commit Records
                             Exit Loop If [ $Date = RentalINvoices::EndDate ]
                             Set Variable [$Date ; Value: $Date + 1 ]
                         End Loop
                         Go to Layout [Original Layout]
                       Else
                          Show Custom Dialog ["Please enter a valid start and end date before clicking this button."]
                       End If
                        

                       Note: When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                        

                  • 6. Re: Calulation for date range..assigning custom values based on variables
                    jl2012

                         I appreciate your patience with me.  I am new to this level of programming in FileMaker, so this is taking a bit longer for me to grasp.

                         So I created the looping script you posted.  When I run it by itself, it just keeps generating records in the RentalDates table.  The only way to stop it is to hit Apple Period or Command Period.  So I am confused by a few things:

                         I assume that I will need to apply this script to my RentalInvoice Table/Layout somehow?  It would be great if the script would run when I create a new record in my RentalInvoice table and assign a StartDate and EndDate for a guests stay.  I'm still a little confused on how to get the proper response back into my RentalInvoice table, generating the rate information in a field i.e. 3 Nights at $160.00 ($480.00).  I am assuming once the looping script works, I would have to create a calculation in said field, possibly using and If scenario, to generate the proper response?  The one thing I am still really confused on with the looping script is how does it delineate between different days of the week?  We have different prices for Friday and Saturday, throughout the year and I am not seeing how the looping script picks this information out.  Further more, I can't figure out how to add this information to my RentalRates table.  I feel like I am close here, but still missing pieces of the puzzle.  I am attaching an image of the script I generated in case there are any errors.  For my solution, RentalInvoice is ZuluSampleEvents.  Just wanted to make that clear for you.

                         Thanks in advance.  If I can get this working in my solution, it will save me hours of time down the road.  I can't tell you how amazing that would be.

                    • 7. Re: Calulation for date range..assigning custom values based on variables
                      jl2012

                           Sorry, here is the image! :)

                      • 8. Re: Calulation for date range..assigning custom values based on variables
                        philmodjunk
                             

                                  I assume that I will need to apply this script to my RentalInvoice Table/Layout somehow?

                             No. It creates records in RentalDates. It may help you to put a portal to RentalDates on your zulusampleevents layout, but you need not actually do that for this script to work.

                             

                                  It would be great if the script would run when I create a new record in my RentalInvoice table and assign a StartDate and EndDate for a guests stay.

                             That's exactly what we want it to do, but to start simple, I've suggested that you add a button on your RentalInvoice layout to click after adding start and end dates. Once it is working for you, we can set up a script trigger on the EndDate field to perform this script if you prefer that option.

                             

                                  I'm still a little confused on how to get the proper response back into my RentalInvoice table,

                             Once the records have been created in RentalDates, a calculation field in RentalInvoice can use Sum to total the rate field in RentalDates that looks up the daily/weekly rates from the RentalRates table.

                             

                                  how does it delineate between different days of the week?

                             That's a mistake on my part as I overlooked that detail. The script will not distinguish between days of the week, but we can modify the relationship used to look up the rates to do so. Essentially, dates for Friday - Saturday will look up rate from a different record than that used for the rest of the days of the week.

                             Your script looks perfect. So I'd check two other parts of this system that if not set up correctly could keep your script from working correctly. The startdate and enddate fields must be fields defined as type date. You can check this in Manage | Database | Fields. And in Mange | Database | Relationships, you need a relationship between ZuluSampleEvents and RentalDates that reproduces the relationship that I specified between RentalInvoices and RentalDates in my exampe. (the Exit Loop If step needs this relationship in order to get the correct value of EndDate to see if enough RentalDates records have been created.)

                        • 9. Re: Calulation for date range..assigning custom values based on variables
                          jl2012

                               Okay...so I created a button in my ZuluSampleEvents database to activate the script when pressed, to start out simple.  I also checked my relationships between tables.  For the life of me, I can't figure out why the script just keeps generating records in the RentalDates table and not stop on the actual DateEnd selected on the record.  I am attaching my relationships in hopes that it will clear some things up here.

                               So how do I add the Friday/Saturday rates to my RentalRates table without creating duplicate records with conflicting information?

                               I appreciate your patience.  I am trying to figure this out without constantly asking questions, just can't quite wrap my head around it for some odd reason!

                                

                          • 10. Re: Calulation for date range..assigning custom values based on variables
                            jl2012
                            /files/72f70a64c5/Relationship_2.jpg 642x757
                            • 11. Re: Calulation for date range..assigning custom values based on variables
                              jl2012
                              /files/1f7c48fcb9/Relationship_3.jpg 642x780
                              • 12. Re: Calulation for date range..assigning custom values based on variables
                                philmodjunk

                                     You don't have the correct relationship linking zulusampleevents to RentalDates. They should only match by ID fields, not by anything else. This is why your script fails to stop looping.

                                     The relationship between RentalDates and RentalRates can be modified so that you can specify different rates for different days of the week if you add yet one more match field pair to the relationship:

                                     RentalDates::Date > RentalRates::StartDate AND
                                     RentalDates::Date < RentalRates::EndDate AND
                                     RentalDates::Type = RentalRates::Type AND
                                     RentalDates::cDOW = RentalRages::DOWList

                                     define cDOW as a calculation field with this expression:

                                     WeekName ( Date ) //select text as the result type

                                     Define DOWList as a text field. On the layout where you assign values to it, format it with a group of check boxes with the names of the days of the week. This way, you can set up one rate record and click the Friday and Saturday check boxes to set up the rate for that date period for Friday Saturday rentals and then you can set up a second record where you click all the check boxes except Friday and Saturday to document the rates for all the other days of the week.

                                • 13. Re: Calulation for date range..assigning custom values based on variables
                                  jl2012

                                       So I deleted all of the relationships except for the ID's...see attached.  I ran the script again and it still fails to loop.

                                       Also if I am only relating ID's then when you mention the relationships between RentalDates and RentalRates, what exactly are you referring to?  I am using Filemaker 13, don't know if this is where some of the disconnect might be.  Completely lost on how these relationships are supposed to be configured to make the script function properly.

                                       Sorry.  Really confused and frustrated.

                                  • 14. Re: Calulation for date range..assigning custom values based on variables
                                    philmodjunk

                                         Still not the correct relationships. The relationships between RentalDates and RentalRates where correct--at least as far as I had specified them until my last post where you add one more pair of fields. And a RentalRatesID field is NOT one of those match fields.

                                         But what you show reveals a problem with RentalDates::_fkRentalInvoiceID. There should be "crow's feet" on that end of the connector line. I would guess that you defined _fkRentalInvoiceID to auto-enter a serial number. Please remove that setting as this breaks the relationship between the left two tables. It must get a matching value from zulusampleEvents::__pkRentalInvoiceID.

                                    1 2 Previous Next