9 Replies Latest reply on Mar 24, 2016 8:29 AM by BillisSaved

    Having one Portal update two tables?

    t_e_x

      I am trying to create a layout in which the user can rapid input various designated dates associated with a job.  I have a global table to house the global variables to filter the records for the portal. 

      Screen Shot 2016-03-22 at 10.39.41 AM.png

      The portal is setup with the following fields based on Job_Dates_Quick:

           Job_Dates_Quick::_kf_JobID

           Job_Dates_Quick::Date

           Job_Dates_Quick::DateType

           _Global_QD_Jobs_Dates_Job_Emp::Emp_Initials

           _Global_QD_Jobs_Dates_Job_Customer::CustomerName (if verfication, no data entry)

       

      The all of the field correctly display data that exists in the table.  However, I an unable to input Emp_Initals unless a value already exists for new records into Job_Dates_Quick.

       

      How would I go about being able to input data in Emp_Initials?

       

      I would appreciate any guidance.

       

      Thank you,

       

      Michael

        • 1. Re: Having one Portal update two tables?
          Mike_Mitchell

          I'm not 100% certain what you're trying to do here, but it seems like you're trying to assign an employee (and presumably, a customer) to a job. If this is the case, the object isn't to enter data into the two tables on the far right of your Graph. Instead, the objective is to create a record in _Global_QD_Date_Job and assign it the appropriate customer and employee values.

           

          This should probably be a scripted process where you allow the user to assign the desired values in global fields, then create the record after the selection is submitted.

          • 2. Re: Having one Portal update two tables?
            t_e_x

            My intent here is to allow the user to rapidly input dates into the Job_Dates_Quick table through a portal.  However, in this instance, the date type would be "Due Date" and hey would need to assign an employee to the job.  The employee is designated in the _Global_QD_Jobs_Dates_Job_Emp::Emp_Initials, which is another instance of my "Jobs" table.

             

            The customer for the job in already assigned in "Jobs" table, and the reference in the portal is just for user verification, not input or modification.

            • 3. Re: Having one Portal update two tables?
              BillisSaved

              Good afternoon t_e_x,

               

              I hope your day is going well. I want to clarify in my own mind what your design intention is before I offer my opinion. As Mike mentioned, it appears you have an Employee and/or a Customer that are associated with a specific job. Each Job has multiple possible Date Types that could be associated with it. Am I correct so far?

               

              God bless,

               

               

              Bill

              • 4. Re: Having one Portal update two tables?
                Mike_Mitchell

                t_e_x wrote:

                 

                My intent here is to allow the user to rapidly input dates into the Job_Dates_Quick table through a portal.  However, in this instance, the date type would be "Due Date" and hey would need to assign an employee to the job.  The employee is designated in the _Global_QD_Jobs_Dates_Job_Emp::Emp_Initials, which is another instance of my "Jobs" table.

                 

                 

                Based on your Graph, I'd say this is in error. Your employee is designated by EmpID, not initials, isn't it? Aren't the joins between tables based on ID, not initials? (If not, they should be.)

                 

                Anyway, with this description, I stand by my initial assessment. You need a scripted process to insert the EmpID into the Jobs table. If you're trying to add dates to a job (which is what your Graph looks like), then the job has to be there prior to assigning an employee to it. This is probably what FileMaker is telling you. You need a scripted process to insert the EmpID into the Jobs table, if necessary creating the new Jobs record at that time.

                • 5. Re: Having one Portal update two tables?
                  t_e_x

                  BizPraxis wrote:

                   

                  Good afternoon t_e_x,

                   

                  I hope your day is going well. I want to clarify in my own mind what your design intention is before I offer my opinion. As Mike mentioned, it appears you have an Employee and/or a Customer that are associated with a specific job. Each Job has multiple possible Date Types that could be associated with it. Am I correct so far?

                   

                  God bless,

                   

                   

                  Bill

                  My day is going well, thank you.  I hope yours is going well too.

                   

                  You are correct so far in your description.  To provide more insight, I have trying to design a timeline for the job from start to finish.  So each job will have several dates associated with it (Received, Written Up, Assigned to Employee, Completed, Reported, Invoiced).  We have instances when a job can have several due dates, and I would like those in the timeline as well.  This is the rational for splitting the dates off into there own table.

                   

                  The issue is that I am trying to create a layout to facilitate a "rapid input"  for the date fields.  I would like to be able to go a specific portal and be able to type the job id, date type, and the date; without navigating to each individual record.  The date type "Due Date" is where we assign the employee to the job.  To clarifiy Mike_Mitchell question, the user does not use the Emp_ID field, it is internal to the database.  The employee will be accessed through a value list.

                   

                  I hope I am presenting this clearly for you.

                  • 6. Re: Having one Portal update two tables?
                    t_e_x

                    Also, on a side note, all of the entries for this "Rapid Input" date form are being entered on already existing job records.  The job must already exist to enter dates.

                    • 7. Re: Having one Portal update two tables?
                      erolst

                      As Mike has suggested: script this.

                       

                      Put a button into the portal(s) that triggers a script which

                       

                      stores the ID of the clicked job in a $$global

                      opens a popover

                      accepts input into some global fields

                      on OK, creates a new date entry based on the stored jobID and the user input, then resets the globals and deletes the $$global; on Cancel, only does the latter.

                       

                      Scripting is usually the best approach for complex systems; here, it allows you to pre-check the user input, and e.g. only allows certain types of events to be added, based on existing data.

                       

                      Adding data inside a portal only allows you to do such checks post factum, which can be a bit messy, and is an inferior user experience.

                      • 8. Re: Having one Portal update two tables?
                        Mike_Mitchell

                        t_e_x wrote:


                        You are correct so far in your description.  To provide more insight, I have trying to design a timeline for the job from start to finish.  So each job will have several dates associated with it (Received, Written Up, Assigned to Employee, Completed, Reported, Invoiced).  We have instances when a job can have several due dates, and I would like those in the timeline as well.  This is the rational for splitting the dates off into there own table.

                         

                        The issue is that I am trying to create a layout to facilitate a "rapid input"  for the date fields.  I would like to be able to go a specific portal and be able to type the job id, date type, and the date; without navigating to each individual record.  The date type "Due Date" is where we assign the employee to the job.  To clarifiy Mike_Mitchell question, the user does not use the Emp_ID field, it is internal to the database.  The employee will be accessed through a value list.

                         

                        If this is the case, first of all, your data model is wrong. If you have a need to have individual employees (different ones) assigned to each date, then you need the EmpID field in that table (Job_Dates_Quick). Otherwise, you have no way to assign a different employee than the one who's been assigned to the parent job.


                        Yes, I said EmpID, not initials. You need to use the key field, regardless of whether it's presented in a value list. Otherwise, you break your relational structure. Unless you don't care about being able to access the employee's information from the job assignment - which would be a shortsighted design.


                        If you just insert the EmpID via a value list (perhaps using a pop-up menu, so the ID is hidden), and have a TO pointing back to Employees from there, you'll have access to all information. In fact, it will make it much simpler (since you can use a field in that portal).


                        Alternatively, if you don't like pop-up menus, use a scripted approach, as has already been stated.

                        • 9. Re: Having one Portal update two tables?
                          BillisSaved

                          Good morning t_e_x,

                           

                          I hope your day is going well. I apologize for taking so long to reply; I've had a crazy week thus far. Based on this on going discussion, it seems that the central component, or hub if you prefer, of the solution you're creating is the Job. I think you may find the goals you're attempting to achieve a bit easier if you modify your schema to look something like this simplified example:

                           

                          2016-03-24_10-28-18.png

                           

                           

                          If you configure the relationships to allow creation of records in the child tables, you could base all of your layouts on the Job table and use portals to provide quick entry of other information. Of course changing schema is not always possible, or simple, but I think your life as a developer might be easier after it's complete...just my opinion. Good luck!

                           

                          God bless,

                           

                          Bill

                           

                          EDIT: You may also want to consider adding an additional JobDates table occurrences as a children of the other tables, depending on how you want your JobDates data to be presented to the end user. Or, if you're comfortable with the model, implement the Selector/Connector design method - See Geist Interactive for more information.