1 2 Previous Next 24 Replies Latest reply on Jul 28, 2012 9:13 PM by philmodjunk

    Help creating roundtrip

    4guito

      Title

      Help creating roundtrip

      Post

      Hello all,

      I create the first leg of a round trip. I would like to be able to click on round trip button and create the second leg.

      1- do I need to create a duplicate layout so that 2nd leg will be create on that duplicate layout.

      2- How the adresses be switch in the second leg.

      I can post a screenshot if need be.

      Please help!!

        • 1. Re: Help creating roundtrip
          philmodjunk

          Can't help withoug knowing a great deal more about your database.

          HOW do you create the "first leg"?

          How does the "return leg" differ from the "first leg"?

          • 2. Re: Help creating roundtrip
            4guito

            Here is a screen shot of the database. On the return leg,I want the pickup address and the drop-off address to switch.

            Thanks

            • 3. Re: Help creating roundtrip
              philmodjunk

              What tables do you have? Assuming more than one table, how are they related?

              This could be as simple as:

              Duplicate Record
              Set Variable [$PickUpAddress ; value: YourTable::PickUpAddress ]
              Set Field [YourTable::PickUpAddress ; YourTable::DropOffAddress ]
              Set Field [YourTable::DropOffAddress ; $PickUpAddress ]

              Use additional variables and set field steps for any other pairs of fields where the data needs to be "swapped".

              But I am assuming a great deal about your database design that could be incorrect and that could keep this from working without additional script steps.

              • 4. Re: Help creating roundtrip
                4guito

                I have a table for the patients, a table for for the clinics, and a table for reservation. Also do I need to create a duplicate layout so that the second leg is created in that duplicate layout?The 2 circled fields are in the reservation table. Is that where I have to place the variables?

                Also if the patient has more than one pickup address. How do I set that up? Creating a new record for the second address is not the right way. right?

                 

                Thanks

                • 5. Re: Help creating roundtrip
                  philmodjunk

                  I have a table for the patients, a table for for the clinics, and a table for reservation.

                  In in what table do you define the fields that you have circled?

                  Also, since you already display the patient and clinic address, why do you need to list them a second time?

                  How have you linked these tables in relationships?

                  I can't answer your question about a dulicated layout without knowing more about your database.

                  Also if the patient has more than one pickup address. How do I set that up? Creating a new record for the second address is not the right way. right?

                  What does more than one pickup address mean? That sometimes the patient is picked up from location 1 and sometimes from location 2? If so, it would seem you need a related table of pick up locations.

                  • 6. Re: Help creating roundtrip
                    4guito

                    Hello Phil,

                    The 2 circle fields are in the patients tables.

                    I didn't have to put them there. It's just a block address that I also use in my dispatch grid (portal), instead of having the address, city, state, zip separately.

                    If I use your variables, I won't need a duplicate reservation layout. I just didn't know how to do the reverse step. Do I need to create a script for these variables, or do I need to write them inside the 2circle fields. Also I would like to pickup time to be 12:00AM and drop-off time to be blank since I won't know what time they will be finish with their Dr appointment.

                    Sometimes a patient might want to be pickup from their office instead of home. How do I create that 2nd address without creating a new record for that patient? When creating  a new reservation, I would like to have the option to pick between the two or maybe three addresses.

                     

                    Thanks

                    • 7. Re: Help creating roundtrip
                      philmodjunk

                      If it is possible for one patient to have multiple pick up and drop off locations, then a related table, linked to patients by Patient ID should be used to list locations. Then selecting a LocaitonID should link that record to your Reservation record.

                      It looks like you need to duplicate the reservation record, but specify pickup and drop off locations in Reservation rather than patients by using the links I just described. You'll need multiple occurrences of your locations table.

                      Patients::_kp_Patients_Id = PatientsLOCATIONS::_fk_Patients_Id

                      Reservations::_fk_PickUpLocationID = ReservationsLOCATIONSpu::_pk_Location_Id
                      Reservatiosn::_fk_DropOffLocationID = ReservationsLOCATIONSdo::_pk_Location_Id

                      In Manage | Database | relationships, make a new table occurrence of PatientsLOCATIONSby clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be ReservationsLOCATIONSpu.

                      We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                      Then repeat for the third table occurrence: ReservationsLOCATIONSdo

                      And my sample script shared earlier could be used to swap the values in _fk_PickUpLocationID and _fk_DropOffLocationID.

                      BUT now I get what you meant by your question about a duplicate layout. It could work to use one layout for Pickups and one layout for drop offs where you simply switch the labels for the pick up and drop off locations.

                      • 8. Re: Help creating roundtrip
                        4guito

                        Ok, Something is working, I used the variable and it create a new trip with a new confirmaton# and a new Trip#, with the reverse route. BUT when I click on the records button back and forth to double check, the only fields that changed is the trip# and the reservation# not the addresses.and when I check in the portal it doesn't switch either.

                        • 9. Re: Help creating roundtrip
                          4guito

                          Hi Phil,

                          I see that you are leading me to the right direction> I'm showing you the whole relationship of the database.

                          Base on that structure, do I need to label my ReservationsLOCATIONSpu---puADDRESS, puCITY, puSTATE, puZIP

                                                                                          ReservationsLOCATIONSdo---doADDRESS, doCITY, doSTATE, doZIP

                          Do I still need to add address, city, state zip fields in the patients table?

                           

                          And my sample script shared earlier could be used to swap the values in _fk_PickUpLocationID and _fk_DropOffLocationID.

                          What data is going into these 2 field? The full pickup and dropoff address?

                           

                           

                          Please Help

                          • 10. Re: Help creating roundtrip
                            philmodjunk

                            do I need to label my ReservationsLOCATIONSpu---puADDRESS, puCITY, puSTATE, puZIP

                                                            ReservationsLOCATIONSpu---doADDRESS, doCITY, doSTATE, doZIP

                            No. ReservationsLOCATIONSpu and ReservationsLOCATIONSpu are two occurrences of the same data source table. Since they refer to the same table, their field names are identical.

                            Do I still need to add address, city, state zip fields in the patients table?

                            These fields should be removed from the patients table. Since the patient can have two locations, we are replacing them with multiple records in the Locations table. (A Patient can even have more than two locations.)

                            What data is going into these 2 field? The full pickup and dropoff address?

                            These fields would only contain what their field names indicate: an ID number as they establish links to the locations table.

                            Note that Clinic locations would also be entered in the locations table. Multiple patients records would all link to the same clinic location if they are patients of that clinic.

                            • 11. Re: Help creating roundtrip
                              4guito

                              Hello Phil,

                              So far everything working great, but I just don't know how to ling these 2 occurence tables.

                              Also on the reservation page I use value list to pull the pickup and drop-off address, I'm not getting the full address, city and zip are not showing, just the address show.

                               

                              Thanks

                              • 12. Re: Help creating roundtrip
                                4guito

                                This is the screenshot for the occurence tables

                                • 13. Re: Help creating roundtrip
                                  4guito

                                  Hello Phil,

                                  I realized that I uploaded the wrong pics. The 2 shaded tables, where should they be link to? and my second question how can I show the full address in the reservation layout for _kf_PickUpLocationID and _kf_DropOffLocationID?

                                   

                                  Thanks

                                  • 14. Re: Help creating roundtrip
                                    philmodjunk

                                    Why do you need the additional occurrences?

                                    The full address should come from fields in the locations table. You should be able to add those fields (specifying the correct table occurrence in each case), to your reservations layout and when you select a location, the address info appears automatically.

                                    And a conditional value list can limit the listed locations to just those linked to a specfic patient.

                                    PS. since making my last post, I've realized that linking patients to a location by PatientID may not be the best option. It works just fine for their home, office, etc locations, but is not ideal for linking to a clinic location which is likely the same location for many different patients. What I've suggested works, but results in many duplicate records for the same clinic--which could be a major update nightmare if a clinic were to relocate.

                                    1 2 Previous Next