12 Replies Latest reply on Sep 21, 2010 6:19 AM by Bricktop

Help - Start date and End date total rental cost?

Title

Help - Start date and End date total rental cost?

Post

Hi All,

I need some help with a start date and end date calulating the days for a rental charge.

If Item "A" rents from 09/12/10 (drop down calendar) to 09/22/10 (drop down calendar) equals 10 days

Rate of item "A" is per day is \$1.00

Rate of item "A" is per week is \$4.00 or equal to 4 days

so a 10 day rental would be \$7.00

I need help to get the calculations to work out right and get a total. I have an Items table and an invoice table related through a lines table.

I hoping someone can please help!! Ive been up all night! Thanks

• 1. Re: Help - Start date and End date total rental cost?

I don't know how you have the prices defined for the Items.  Basically, it would look something like:

Div ( Invoices::cNumberDays ; 7 ) * Items::WeekPrice + Mod ( Invoices::cNumberDays ; 7 ) * Items::EachPrice

• 2. Re: Help - Start date and End date total rental cost?

Here is a sample file which shows how I would probably structure it.  However, I do not know your business rules, ie, whether the rental range applies to the Invoice or whether it applies to each item (indicating that each item can have a different rental period) so you still may have to move the rental period to the LineItem.

http://www.4shared.com/file/1gKZzqWa/Rentals.html

If, on Invoices, you add a LineItem (by selecting from the LineItems pop-up), the calculation will display the computed result I provided above.  Keep in mind that, if you change the rental period on the Invoice, the lineitems will NOT all change accordingly.  They are set as numbers which auto-enters the rates.  This is because, if down the road, you change your rental rates, you don't want all of your lineitems on older invoices to also change.

So if you allow changing a rental period (and it is on your invoice) after lineitems have been entered, you will need to script updating your items.  Your other option is to insert the Rental period within each lineitem but then if you change the rental period for all items, you would still have to update your lines (by updating the rental dates on each line).

• 3. Re: Help - Start date and End date total rental cost?

Thanks LaRetta for replaying. Yes on Invoices I want to be able to change the items rental dates at anytime, ie-returing early or keeping item longer. I was thinking I was going to have start date and end date in my line table. That example looks correct but its locked so I cant see the calulations.

• 4. Re: Help - Start date and End date total rental cost?

Uh, that file isn't locked at all.  You might check the OS settings to see if it was locked at the file level after you downloaded it?

Since your lineitems might be returned at different times, then the demo file would still work - just switch the calculation (and start and end dates) to your LineItems table instead.  Maybe you can try to download the file again ... if you downloaded it immediately, maybe the upload wasn't finished yet.

If you still have problems grabbing the file, I would be happy to upload it again or email it to you (you could private message your email to me; please don't post it in the thread) or I would be happy to send you MY email address.  But the demo should help you quite a bit so I hope you can grab it!

• 5. Re: Help - Start date and End date total rental cost?

Cool thanks it worked I had to download it again. It was a BIG help.Wow thank you so much!!  You set me up with the hard part, I'm new to FMP.  If I can throw one problem,  along the same lines, what if I have........

item type -a  cost 1.00 day 4.00 for the week from vendor z

item type -a cost 1.25 day 5.00 for the week from vendor x

So when I invoice the customer (me middleman)

I want to pick the item, pick the vendor where i get it from, and pick the start and end dates and it calculates

that's my full problem.... do you think I need a record per item which is the item with vendor name or can I have the same item on a list with different vendors and then the price calculates.

It seems like your sheet I would make one record for each item which would be unique per vendor correct?

I hope I'm not confusing you

Thanks

• 6. Re: Help - Start date and End date total rental cost?

Big Help Thanks Alot it takes me a while but Ill get the hang of it!!

• 7. Re: Help - Start date and End date total rental cost?

I tried to add a mothly price and it didnt seem to work uuhhhgggggg!!! Im really trying to figure out.

Div (rental length ; 28 ) *equipment::price monthly + Mod (rental length ; 7 ) *equipment::price weekly + Mod (rental length ; 7 ) * equipment::price daily

28 days or 4 weeks is montly rental

where is it wrong?

• 8. Re: Help - Start date and End date total rental cost?

I almost missed this - I wish they would fix this forum so we can spot posts easier.

There are several ways of writing this but see if this makes it clear:

Let ( [
n = c_numb_days ;
m = Div ( n ; 28 )  ;  // month
w = Div ( n - m * 28 ; 7 ) ;  // week
d = n - ( m * 28 + w * 7 )  // day
] ;

m * Items::MonthPrice + w * Items::WeekPrice + d * Items::EachPrice
)

• 9. Re: Help - Start date and End date total rental cost?

Thanks so much AGIAN!!! I will give that a try when I get home, I'm at my "real job" now.

With this same invoice solution I was originally making a record in my items list with the 3 rates (D/W/M)
but its alot of item-records to sort and I have to create alot of records. Its design friendly (easy to make) but not user friendly (alot of records to sort through

Does it make more sense to make a separate price list table with 4 vendors and lots of the same items, different prices so the user on invoice simply pick from individual drop down menus, one vendor, one item, the list calculate the c_numb_days to give me the price
it seems like it would be more user friendly but I'm just worry, if its beyond my skill, if the usage reports will be more or less accurate, and if I can mix with this great formula that you just helped me with and the tweaking I going to have to do.

Thanks Again
All this help is greatly appreciated

• 10. Re: Help - Start date and End date total rental cost?

There would be many things to consider in such a decision and a big one is what you mentioned ... how comfortable you are with FileMaker at this point.  Pricing structures can get quite complex and it also depends upon your business model.  For instance, consider these questions:

1) When a User wants to select a product, do they want to see only the product representing the best price?
2) What is considered 'the best price' when we are comparing days, weeks and monthly rates?
3) Would the User prefer to select a Vendor with a higher price because the User knows the Vendor is better?
4) How many different Vendors might you incorporate for a given product?  How many products are you anticipating (triple what you expect to get your starting point).

You can have your User select the product.  Then you can select a second pop-up which lists all the vendors and their prices for that product ... or you can have your User select the Vendor and then all of that vendor's products will show in the next pop-up.  These types of 'double-field' selections are known as conditional value lists.  So your system will get more complex (depending upon your business answers to the above).

• 11. Re: Help - Start date and End date total rental cost?

Well not real comfortable, but I like a challenge....  I'm going to create an equipment_ID list with 4 different vendors with 12 prices per equipment _id, daily-weekly-monthly x 4 vendors.

Price choosing isn't a problem its just customers choice.

My goals:
1) easy user interface
2) impress bosses
3) make my own life easier by pulling us away from badly created excel spread sheets
4) learn something new

Steps for my invoice solution:
1) click new invoice
2) pick equipment
3) pick Vendor
4) pick the start and end date   ****price is calculated****

that's it pretty simple

or at least I thought ....(50 hr in)

• 12. Re: Help - Start date and End date total rental cost?

So I was close to finished when I started to check eveything this calculation didnt work out all the way. I tried to work it out with "<" and ">" I just cant get it add it up right. 4 days is equal price to 7 days, 3 weeks is equal to a month and 28 days is one month rental.  SO if i had an item at \$1.00 a day 4 days or 7 days equals \$4.00.  8 days is \$5.00  but 11 to 14 days is \$8.00

Let ( [
n = rental length ;
m = Div ( n ; 28 )  ;  // month
w = Div ( n - m * 28 ; 7 ) ;  // week
d = n - ( m * 28 + w * 7 )  // day
] ;
Case ( vendor="Company A";
m * equipment::company a_price monthly + w * equipment::company a_price weekly + d *equipment::company a_price daily;

Case ( vendor="Company B";
m *equipment::company a_ monthly+ w *equipment::company b_weekly + d *equipment::company b_price daily;

I hope this all clear