5 Replies Latest reply on Jul 30, 2013 6:37 AM by BruceHerbach

    Quote into Reservation

    gbprudente

      Hello TechNet!

       

      I am a little lost in my solution regarding my approach. I am trying to get a solution that builds transportation quotes for clients, then transform them into bookings/reservations.

       

      My current structure/thought process is:

       

      Quote------<Quote_Transportation------<Quote_TransportationService-----<Quote_TransportationItinerary

       

      Reservation------<Reservation_Transportation------<Reservation_TransportationService-----<Reservation_TransportationItinerary

       

      Some of the questions that have been haunting me are:

       

      1) A person can make a reservation straight away, without having to quote - which means they would have to have almost all the same fields(with the exception of quote-only fields), to handle any situation. Would it be best to keep both under the same table with a flag field to differentiate between Quote/Reservation?

       

      2) If I keep them into separate tables, for the sake of isolating them better, what would be the best way to convert parent/child records from one to the other, in a reliable manner? I know we can do export/imports, and I've also used a few different techniques, I'm just worried this might not be effective when 20+ people are using it at the same time.

       

      3) When assigning a client to a quote, I'm using a drop-down list with auto-complete, to make it user-friendly. However, I am doing a lookup on the foreign key based on that name match coming from the client names value list, and I know that isn't good at all because if the client gets his name changed, it won't update towards the quote. How can I keep the auto-complete while assigning the number foreign key effectively??

       

      I will appreciate any answer, opinion or thought on this A LOT! Thank you for your time and sorry for the long post.

       

      Att,

      Guilherme.

        • 1. Re: Quote into Reservation
          erolst

          Hi Guilherme, welcome to this nice forum!

           

          1) I know there are different schools out there, but I'm a proponent of the "keep together what is very similar" approach; meaning keep quotes/reservations/bookings in the same table. It's almost always easier to filter stuff out that you don't need than - comes reporting time - to combine required components from separate locations into an auxiliary table; not to speak of the structural overhead. This becomes even more obvious if - instead of or in additioon to reporting - you have a dashboad-type controlling/reporting layout with portals.

           

          Add a TransactionType (or whatever you call it) with just one record for quote, reservation, and booking, respectively, and use it to flag the transaction records.

           

          On a related note: this structure is immensely flexible. Should you ever offer a different type of service, in addition to transaportation, you'd create a Services table, create two records for Transportation and your new service, flag all existing Transactions as belonging to reservations, and finally set up an auto-enter calc so new records will belong to the correct service. That's about it - you just add a higher level to the ediifice and add some new wiring, and the existing, lower floors remain entirely undisturbed). Now imagine doing the same expansion within a separated structure …

           

          2) Save yourself from that headache. If your quote becomes a reservation and eventually a booking, and your transactions are in one table, then just find the correct record, duplicate it and convert the duplicate. This way you have - within one single table - a complete transactional history (on the booking level, et least) for your entire business, which easily facilitates all sort of reports and summaries (per date/client/staff etc.).

           

          3) You could use a filtered portal (based on a purpose-built TO to Clients) with a script trigger OnKeystroke; this gives you a list of finds, and when the user clicks to select, what you actually use is the clientID. See this article.

          If you don't want to see the portal on your layout all the time, you could create a picker layout with this portal and display it in a new window, like a dialog box.

           

          Hope this gives you some inspiration.

          1 of 1 people found this helpful
          • 2. Re: Quote into Reservation
            gbprudente

            Thanks for replying, Erolst! You definitely gave me some food for thought.

            erolst wrote:

             

            1) I know there are different schools out there, but I'm a proponent of the "keep together what is very similar" approach; meaning keep quotes/reservations/bookings in the same table. It's almost always easier to filter stuff out that you don't need than - comes reporting time - to combine required components from separate locations into an auxiliary table; not to speak of the structural overhead.This becomes even more obvious if - instead of or in additioon to reporting - you have a dashboad-type controlling/reporting layout with portals.

            Won't filtering stuff out also cause overhead? I know relationship filtering is good, but is it reliable? I keep thinking to myself, in one year, all the records that are going to be in the table, won't it slow down the system significantly in that manner? And yes, I do intend having a dashboard!

             

            Add a TransactionType (or whatever you call it) with just one record for quote, reservation, and booking, respectively, and use it to flag the transaction records.

            Is this what you mean?

            (service is replacing quote/booking - I'm considering booking the final stage, there are no "reservations",at least not now )

             

            Service-----<Transportation-----<Transportation_Service----<Transportation_Itinerary

            -fk_TransactionTypeID ( can be quote/booking )

             

            Service----<ServiceChange ( tracking when it switched from one to the other)

                               -fk_TransactionPreviousID

                               -fk_TransactionNewID

             

            It is important to keep track of when it was approved/rejected by the client and internal office, but what happens in this scenario: Quote for 5 services, client approves it, then after the first service, he cancels the last two. If it's the same record, the quote will then reflect a different value - I would believe it's important to know how the quote was/looked like, and have a different record for the service...right? Or should I duplicate transportation,transportation_service and transportation_itinerary when it switched transaction type too, to track all of them?

             

            Sorry if I couldn't understand your answer 100%

            • 3. Re: Quote into Reservation
              DrewTenenholz

              Guilherme --

               

              I'm going to make a counter-suggestion to erolst about your system.  I think I would prefer two sets of linked tables, one for quotes and the other for actual reservations.  Since you already have a series of linked tables (admittedly parallel), you won't be able to use 'Duplicate Record' and ever get a complete collection of linked parent and child records, especially in a multi-user situation.

               

              Since you already need a method to create multiple records in multiple tables based on turning a quote into a reservation, then I don't see what lumping them all together gets you except a lot of overhead to drive home the message to users that they must ALWAYS, ALWAYS, ALWAYS make sure they know whether they are doing either a quote or a reservation, and that the system is doing the right thing for them.  One slip up, and you're looking at fixing not just the parent record, but the children, grandchildren, and great-grandchildren in the relationship graph you have.  The same goes for reporting, even quick reporting like how many reservations need to go out in the next 15 minutes.  Fifty-Two!!!, no, that's not right, it is four reservations and 48 quotes....

               

              And, you were pretty clear on the fact that there are a couple of fields in quotes that don't apply to reservations.  I'd say the same is true in reservations, where the part about actually getting paid (or not!) matter a whole lot more and you, the bookkeeper, and the accountant will all have an easier time if quotes aren't co-mingled with reservations.  I know you can limit the how searches, portals, and exports are arranged, but to me it seems like more work, not less.  Maybe ask some questions about whether you need to report current and future (or real and proposed) business all in one place, and that will help you decide.

               

              As you describe, having a system that keep the quote and reservation as two different concepts is very useful, since not everything you quote becomes real, and sometimes the reservation has additional items from the quote.  It's good to be able to have that available to your users when there are questions or misunderstandings.

               

              Either way, you need to create a whole set of records from a single button click; in a multi-user situation; on FMServer; and not screw it up.  Export/imports would not be my choice at all.  I'd have a script do the work.  Collect up all of the items you need to create into some sort of 'bucket' and then empty that bucket bit-by-bit into the correct places.  As long as you use $variables (or $$variables) which stay within the scope of the script (or user-based session in the file) , or global fields which stay within the scope of the user's session and any files that can reach them on the relationship graph, you won't end up with collisions and missing data.

               

              My preference is to pass parameters from the button to the main script, from the main script to the sub-scripts, and have them pass parameters back when they've completed their work with a self-created error code so I can figure out if I need to abort the process or clean up.  A while back, the question of how to write a parameter that didn't make you crazy every time you touched it was solved by creating parameters that use names/value pairs and some sort of custom function to set and extract them from a big text block.  I've been using a set from Shaun Flisakowski http://www.spf-15.com/fmExamples/PropertyLists.fp7.zip called 'PropertyLists', but other folks have similar ones.  These folks spent the time to get a really good solution to this, and I cannot thank them enough for it.

               

              Once you can pack & unpack the fields & values you want to set, creating records in any table is just not that hard.  You get the piece you need, go to the right layout, create a new record, fill it in, and repeat until complete.  You can do all sots of error checking, allow the process to continue or not, report the final result to the user, etc., etc.

               

               

              ---

               

              About your auto-complete question:  have you seen the option in pop-up menu to show 'only the second value'?  That was designed specifically to let a user see names while the database records record IDs.  It functions in a type-ahead kind of way in that it does scroll as you type.  It is not a pretty as the type-ahead drop-down list, though.  You can certainly set up a drop-down list and have a, ID field do a lookup for the customer ID based on the name.  Then, base the customer detail fields as a lookup on the ID, and not the name. 

               

              There are also some neat tricks you can play with two overlapping fields, the one behind is available in Browse mode, the one in front is not.  The user basically clicks-through to the data entry field which is recording an ID, but the field on top displays the name.

               

              Neither are a slick as a type-ahead portal, but also don't require a lot of screen real-estate to employ.

               

              ---

               

              Let us know how you are thinking, and there might be consensus on how to approach it.

               

              -- Drew Tenenholz

              1 of 1 people found this helpful
              • 4. Re: Quote into Reservation
                gbprudente

                Wow, thanks for the great answer Drew!

                 

                It took me a while to reply because I had to think about some stuff! I would have to say I have decided to keep quotes and reservations into two diferent tables, mainly because of the scenarios you described.

                 

                But my current script that turns quotes into bookings runs slow - in one of many attempts to find the "best" way of duplicating parent/child records, I have gone with the approach(for this particular case) of creating a new window when dealing with each set of children, that way when I close it, I'm still at the parent record and all I need to do is duplicate and then move to the next. It works, but it is slow(1 quote, 1 quote_transp, 4 transp_services, 20 transp_itin takes around 8-14 seconds).

                 

                ----

                 

                On the auto-complete subject,

                About your auto-complete question:  have you seen the option in pop-up menu to show 'only the second value'?  That was designed specifically to let a user see names while the database records record IDs.  It functions in a type-ahead kind of way in that it does scroll as you type.  It is not a pretty as the type-ahead drop-down list, though. 

                When you use display second field only, you lose the auto-complete from value list functionality in a drop-down list(because first field will be ID, second will be name!)

                 

                You can certainly set up a drop-down list and have a, ID field do a lookup for the customer ID based on the name.  Then, base the customer detail fields as a lookup on the ID, and not the name.

                I was using that, but if the user were to change the name of the customer, this means the name wouldn't be changed for the current existing records - so even though the ID would be correct, the name wouldn't be "updated". Right?

                 

                There are also some neat tricks you can play with two overlapping fields, the one behind is available in Browse mode, the one in front is not.  The user basically clicks-through to the data entry field which is recording an ID, but the field on top displays the name.

                I tried that too, but users are just whining about not being able to type the name and see the auto-complete possibilities(same problem as first suggestion, pretty much).

                 

                On a side-note, I'm thinking on setting an OnObjectModify to force the drop-down list to always open as they type, is that possible? Because they are too lazy to click the arrow, hah

                 

                Thanks for your suggestions guys!

                • 5. Re: Quote into Reservation
                  BruceHerbach

                  Hi,

                   

                  I worked on a setup were I had to duplicate a hierarchy of records.  I experimented with a number of methods and found that the fastest method seemed to be Export/Import using a single window opened at the beginning of the script and closed at the end.  Opening and closing windows as you ran through the hierarchy added a significant amount of time to the duplication process.  Instead of doing a GTRR with new window I would put the parentIDs ( Old and New ) into variables, change layouts and do a find for the parent.  Then Export the record set with just the fields I needed for the new record set. Change layouts to the import layout and import.  In your case export the quote records, change layouts and import them as Reservation records.

                   

                  When you import,  do not import the primary key or the parent foreign key.  If you are using UUIDs instead of standard serial numbers you may have to replace field contents on the primary key.  If you are using standard serial number values they will update automaticly as long as you don't import this field

                  For the new Parent ID I would set up the parentID field to be an auto enter calculation that pulled it's value from the new parent variable. So the new record set would automatically get the new parents ID. 

                   

                  Another effective method was Ray Cologon's Duplicate Hierarchic set.  http://www.nightwing.com.au/FileMaker/demosX/demoX06.html  This was developed for FM10 but will work in 12 and could be adapted to your requirements.