6 Replies Latest reply on Jun 8, 2016 12:54 PM by tays01s

    Combining a dd and popup to set a layout's record

    tays01s

      I have table/layout 'Licence'. Ultimately will have a lot of records so I'd like to be able to copy/paste possible existing emails or auto-complete them. For this I need a drop-down on the 'License::Email' with the value list as all values of the same field. From a previous trick someone taught me, I'd put a pop-up over this in order to display the actual email using License::__ID with the value list from the same field: License::__ID & 2nd field License::Email. There would be an invisible button over the popup to take the user to the dd underneath.

       

      The invisible button script correctly takes me to the dd and this correctly shows the value list initially. However, when I use the dd to set the __ID field the 'on modify' script trigger for:

       

      If [ not IsEmpty (Licenses::Lic_email) ]

      Set Field [ Licenses::__ID; ExecuteSQL ( "SELECT \"__ID\" FROM \"Licenses\" WHERE \"Email\" = ?"; ""; ""; Licenses:: Lic_email) ]

      End If

       

      fails to change record correctly. It appears stuck on one record. Also the dd does not allow 'auto-complete' even though 'browse' is checked.

        • 1. Re: Combining a dd and popup to set a layout's record
          SteveMartino

          If you just want to start typing and see what was already entered, you could, in layout mode:

          1.  Select Field.

          2.  On the Data tab of the Inspector, check the  box  'Auto-complete using existing values'.

           

          Another way, in browse mode while entering data, right click on field, from right click menu Insert> From Index, click on name you want.

          If you want to script it usually I use a modal window.  The window opens, the script pauses.  Then when the user picks the email or License ID, the script resumes gathers the info in a variable, closes the modal and sets the variable in the field with the Set Field script step

          • 2. Re: Combining a dd and popup to set a layout's record
            tays01s

            OK, I've got auto-complete working. But when I use the dd 'on object exit' script

            If [ not IsEmpty (Licenses::Lic_email) ]

            Set Field [ Licenses::__ID; ExecuteSQL ( "SELECT \"__ID\" FROM \"Licenses\" WHERE \"Email\" = ?"; ""; ""; Licenses:: Lic_email) ]

            End If

            it produces "?" in the __ID field.

            • 3. Re: Combining a dd and popup to set a layout's record
              user19752

              You are using only one table, self looking up existing email, then one email have many records. so the result of SQL has 2 or more lines of ID. What ID do you want?

              • 4. Re: Combining a dd and popup to set a layout's record
                tays01s

                The fundamentals of what I need is:

                1. To have a 'dd' field in which I can type an email and have auto-complete 'on'.

                2. Once the email has been found/chosen, I want the script trigger to set the ID field so that the layout displays the chosen record.

                 

                I'm fine about using a related TO if needed, I'm just struggling to adapt the previously used popup (ID & email field) on top of dd (email field) with SQL script trigger indicated above, to this situation.

                • 5. Re: Combining a dd and popup to set a layout's record
                  user19752

                  Sorry I missed you are entering another field from the email. (and you are confusing table name too.)

                  Did you confirm the Licenses:: Lic_email field is text type, same as Email field?

                  • 6. Re: Combining a dd and popup to set a layout's record
                    tays01s

                    I've re-jigged the set up so that:

                    Licenses::Email_lic_find = Licenses 5::Email_lic

                     

                    On the licenses layout, a dd field 'Email_lic_find' with the value list 'Email_lic' and triggers the on modify script:

                    Set Field [ Global::gEmail_lic_find; Licenses 5::Lic_email ]

                     

                    'Global::gEmail_lic_find' is a popover using the valuelist License::__ID & 2nd field License::Email_lic and has an on modify script trigger:

                    Enter Find Mode [ ]
                    Set Error Capture [ On ]
                    Set Field [ Licenses::__ID; Global::gEmail_lic_find ] Perform Find [ ]
                    Commit Records/Requests

                     

                    Separately the dd selection correctly sets and the popover Global::gEmail_lic_find and the popover correctly sets __ID and the License record. However, of course I want all this to work from the dd. Unfortunately if I put the script steps together, all records disappear.