4 Replies Latest reply on Mar 4, 2014 3:30 PM by pmjd

    Advice on Workflow



      Advice on Workflow & Tables


           Hi everyone,

           looking for a few opinions or suggestions on the best way to set up filemaker table relationships for the car hire solution I'm trying to build. Read around a few things and been reading the missing manual but as I'm still starting out I wouldn't mind a bit of advice from some more seasoned users on the best set up.

           At work the way it goes at the moment is as follows:

           1) Take a booking

           2) Once confirmed assign a vehicle on paper planner

           3) Day before rental prep paperwork and take payment inc. deposit unless it's on an account, in which case invoice sent out once rental is complete

           4) Rental ends, assess vehicle condition and return deposit in full or part, sometimes additional charges like fuel or vehicle hired for an extra day etc.

           5) once complete, pay percentage of rental to our head office company.

           In FileMaker I have set up a "booking" table which calculates the price according to the various options selected, which is linked to a "customer_details" table via a join table to allow multiple driver selection. So far so good.

           For the next step I have a "rental" table, which also has fields for the possible extras at the end of a rental as well as lookup fields for various values from the "booking" table, and a link to the "vehicles". As a rental can only have one vehicle but a vehicle can have many rentals I assume that I don't need a join table between rental and vehicle?

           From the rental table I can print an agreement form with all the correct information on it.



           Should I then have a further table which only looks up the values from the appropriate values from the rental table and use this as a "final invoice" table which has all the final details as the one to link with a payment table?

           Booking>---<rental>----<final invoice(all values looked up from rental table)>---<payment join table>---<payments taken

           Would this be a good setup or am I missing something?

           Also any suggestions for vehicle assignment for booking, as in how to avoid double booking a car, or it is only the realm of plugins like seedcode's calendar that would bring that functionality? I know there are a lot of great reviews about it but it's price puts it out of consideration, as well as my limited skills in implementing it!

           Any suggestions or hints very welcome.

           Thanks for reading, 


        • 1. Re: Advice on Workflow & Tables

               I'm going to zero in one just one small part of your description:

               For the next step I have a "rental" table, which also has fields for the possible extras at the end of a rental as well as lookup fields for various values from the "booking" table.

               It sounds like a record in Booking holds all the data needed for any invoicing that you need to do. Instead of adding fields for the extra charges. I'd add a related table so that the extra charges become "line items" in this related table and a portal to that table can be used in Booking to add those charges. This is more flexible than defining extra fields. Should your business model change and you need to add a new extra charge, you can simply add one more record in the table instead of adding another field. This is a lot less drastic a design change.

          • 2. Re: Advice on Workflow & Tables

                 Hi PhilModJunk,

                 thank you for taking the time to look. Good suggestion for the additional items after rental, will definitely implement that as it makes life a bit more flexible. 

                 I did previously wonder about combining the booking & rental tables as suggested due to the common info held. However my main worry with that approach is rental agreement/invoice numbering. Bookings are a bit like estimates, sometimes they fall through or are cancelled, if the rental agreement number (which matches the final invoice number) comes from the booking information the it can potentially leave gaps in the numbering if a booking is cancelled. Subsequent rental agreements would have gaps, which head office/insurance would be wondering about the gaps and just for the sake of neatness I wouldn't like there to be any gaps in the numbering.  

                 With this in mind would my original approach be sound or do you have any alternative suggestions?



            • 3. Re: Advice on Workflow & Tables

                   It's possible to generate a serial number "on command". this value would not be your primary key, an internal serial number field would be generated with each new record and used as a match field in relationships, but this additional field would be set up to get a unique value each time the record changes from "estimate" to "actual" status via a script.

                   This method should be "bullet proof" even if you have several users performing this script at the same time:

                   Let's say that the primary key field for Bookings is named __pkBookingID and is an auto-entered serial number. This is strictly a unique ID used for relationships so "gaps" in the sequence aren't an issue. You don't even have to display the field on any layout unless you need it to debug an issue in your database.

                   Add a new table: BookingNumbers with just two fields: _fkBookingID, BookingNumber.

                   Define _fkBookingID as a simple number field. It's the foreign key to link this record back to a Booking record. Define BookingNumber as an auto-entered serial number.

                   Define this relationship:


                   Bookings::__pkBookingID = BookingNumbers::_fkBookingID

                   Enable "allow creation of records via this relationship" for BookingNumbers.

                   Then, the script to issue a new, unique bookingNumber to a Bookings record is:

                   Set Field [BookingNumbers::_fkBookingID ; Bookings::__pkBookingID ]

                   That script will create a new record in BookingNumbers and that generates the next serial number value the first and only the first time the script is run from any given record in Bookings.

                   You can place BookingNumbers::BookingNumber on any Bookings layout where you need to show the booking number.


              • 4. Re: Advice on Workflow & Tables

                     Thank you very much, the idea is grand and the script is working nicely :)