8 Replies Latest reply on Dec 29, 2016 6:22 PM by philmodjunk

    Portal from different Tables

    Wicktor

      Years ago I did already developed a calendar for appointments for 4 different specialists. It is bases on a single Table with many repeating fields: for each specialist there is one repeating field for work hours, one for client name, one for duration of appointment, one for reason of appointment.

      It works fine but I hate repeating fields and the relationship graph looks like a monster.

       

      I am looking for a more "creative" way for designing a new appointments solution based... I would guess on portals.

      In this case I would have at least 3 Tables: one for Work hours for each specialist, one for Days, and one for Clients.

      I though it was just easy but I am stuck in combining all information together inside one Portal, or, at least one Portal for each Specialist.

      Any suggestion ?

      Victor

        • 1. Re: Portal from different Tables
          philmodjunk

          You might not need a table for days.

           

          Here's a possible data model:

           

          Clients---<Hours>---Specialists

          Clients::__pkClientID = Hours::_fkClientID

          Specialists::__pkSpecialistID = Hours::_fkSpecialistID

           

          A portal to hours on a clients layout would list all appointments and specialists for that client. A portal to hours on a Specialists layout would list all appointments and clients for that specialist.

           

          Both such portals could be filtered to show only the appointments for a specific day and a series of one row portals could be placed on your layout to arrange the appointments shown into a horizontal row as you might be doing with your repeating fields.

           

          And if you decide that you need that Days table, it can be linked by date to the Hours table.

          • 2. Re: Portal from different Tables
            Wicktor

            Hello philmodjunk

            Very good hint as always :-)

            I really didn't think about avoiding the Days Table.

            Thank you for the idea

            Victor

            • 3. Re: Portal from different Tables
              Wicktor

              Hello again philmodjunk

              I reproduced what you suggested...I understand what you mean, it's a great way for showing appointments but (if I am correct) not enough for entering data directly into portals, with all the Hours available on a side and just writing the name of client on the specific hour.

              I am unable to show on the same portal the Hours of availability of the specific Specialist AND the possibility of writing the names of clients for specific hours.

              See attachment of my actual solution (made with repeating fields) with an example for one specialist.

              Victor

              • 4. Re: Portal from different Tables
                philmodjunk

                This is not the case. You can select clients from a specialist base layout by selecting a value for _pkClientID _fkClientID from a value list of names and IDs (with the ids hidden). You can also add a widget for adding new appointments where you click a button and a popover opens with global fields that you complete before clicking a button that adds the new client record (if a new client) and also adds the new Hours record with the correct _fk values to make it appear in the correct place.

                • 5. Re: Portal from different Tables
                  Wicktor

                  Sorry philmodjunk

                  I don't follow you.

                  • 6. Re: Portal from different Tables
                    philmodjunk

                    Do you know how to set up a "use values from field" type value list?

                    • 7. Re: Portal from different Tables
                      Wicktor

                      Hi philmfdjunk,

                       

                      sorry for the delay during holidays.

                      1) Yes of course I can setup value lists from fields, but its not my target.

                      I want a straight data enter layout with all hours listed for each days/each specialist, just as in my actual repeating fields solution that I did years ago (see example I attached on dec 11).

                      2) I don't follow you regarding what ID to calculate based on your suggestion

                      Clients---<Hours>---Specialists

                      Clients::__pkClientID = Hours::_fkClientID

                      Specialists::__pkSpecialistID = Hours::_fkSpecialistID

                       

                      Don't worry if you don't have the time, my actual solution with repeating fields just works fine.

                      I just want to explore if I can reproduce the same user experience with portals but without popovers or anything

                      Victor

                      • 8. Re: Portal from different Tables
                        philmodjunk

                        "Yes of course I can setup value lists from fields, but its not my target."

                         

                        Yes it is your target. I asked to get some idea about how to explain in more detail. To add an appt. for an existing client, you set up the _fkClientID field as a pop up menu with a use values from field value list drawing values from the clients table. Field 1 would specify the __pkClientID field. Field 2 specifies a client name field  This allows you to select a client by name but the value list enters the ClientID.

                         

                        You also have to enable the creation option for your join table.

                         

                        This is just step one but does this make sense thus far?