10 Replies Latest reply on Dec 5, 2012 11:51 AM by philmodjunk

    Adding automated text to a user entered value?

    productionQC

      Title

      Adding automated text to a user entered value?

      Post

           Hi-

           How would one go about entering text to the end of a user entered value.  For example, if a user was to enter the word YES to a field, is it possible to tell fmp to go, or count, to the end of that word and enter another automated word, say "PLEASE".  Thus, the resulting field would now read "YES PLEASE".  Secondly, is it possible for it to see that that word "PLEASE" has already been used in the previous line and now use the next word in a value list, "SIR".  And the next line use say "Ma'am" and so on.  The words I have chosen are for example only, don't try to make logic of why I would be doing this.  

            

           Any Ideas out there?

            

           Thanks

            

        • 1. Re: Adding automated text to a user entered value?
          philmodjunk

               The best way to get help with a question is to go ahead and describe it in terms of the actual values you intend to use. Otherwise, you risk getting a solution that works with the abstract example, but not with the real life issue you are dealing with.

               an auto-enter calculation might do what you want:

               If ( Leftwords ( Self ; 1 ) = "Yes" ;
                    Case ( Not PatternCount ( Self ; "Please" ) ; Self & " Please" ;
                               Not PatternCount ( Self ; "Sir" ) ; Self & " Sir" ;
                               Not PatternCount ( Self ; "Ma'am" ) ; Self & " Ma'am" ;
                               Self
                             ) ;
                     Self
                   )

               And be sure to clear the "do not replace existing value..." option when you add this. But this will probably add just one word each time the record is edited and committed. If you want the entire phrase added each time, you might need to set up a recursive custom function or use a looping script. You might also consider keeping the data entry field separate from a text or calculation field where the entire phrase is produced. Using a text field separate from the data entry field, you could use a looked up value setting to copy stock phrases from a related table of such phrases and you might even format the data entry field with a drop down list of such words and phrases to better facilitate data-entry.

          • 2. Re: Adding automated text to a user entered value?
            productionQC

                 Ok-

                 So I have a script attached to a button on the master layout (table 1).  When the user pushes it, my database creates 4 new records through a portal related to table 2, in table 1.  Each record has a forced serial number as well as the manual number we assign via table 1.  So if we assign the number 123456 to our record in table 1, then the 4 records created in table 2 are all 123456 as well.  We then distinguish each of the 4 test (records) by test type.  So each record in table 2 should read 123456TA, 123456TB, 123456TC, 123456TD  The next record in table 1 might be 1235, thus the process repeats for the four new fields in table2; 123457TA, 123457TB etc.  I have it setup that the user enters the main test number, say 1234 on table 1.  They then hit the button to populate the portal.  The four records are generated.  At this point the user goes to a dropdown (in the portal)  and selects a letter TA, TB, TC etc.  The dropdown is the entire alphabet with a "T" in front.  The two are then combined in a calculation field to combine them as one to read as 1234TA.  I would very much like to take the process of using the dropdown to select a letter out of  the users hands, rather having my scipt on the button not only create the four records, but assign the letters automatically to the end.  I have seen other questions like this, but I don't know what the outcome was and how they went about it.  Your above script looks like it would work, but only if the user was going to type the same entry every time.  That is my fault for using an example that does not properley represent what we are doing.  So is there a way to modify that script to just grab the value from TABLE 1 field, no matter what it is and add our letter coding to the end?

                  

                 Thanks

                  

            • 3. Re: Adding automated text to a user entered value?
              philmodjunk

                   I would add a number field, seq,  to table 2. Each record would show a number sequence staring with 1 in this field.

                   Your calculation that combines the foreign key (123456) with the text could then be:

                   ForeignKey & "T" and Char ( Code ( "A" ) + seq - 1 )

                   You can then use a self join relationship and an auto-enter calculation to assign values to seq in each record.

                   The self join relationship:

                   Table 2::ForeignKey = Table2SameKey::ForeignKey

                   The auto-enter Calculation:

                   Max ( Table2SameKey::Seq ) + 1

                   clear the "do not evaluate if all referenced fields are empty" option in the specify calculation dialog.

                   clear the "do not replace existing value..." field option.

                   Warning: This works well as long as you never have two users logging new records in Table 2 for the same parent record in Table 1. If two users add new records for the same parent at the same time, duplicate values are possible. But I think record "edit" locking will keep this from happening if the only way a user can add a new table 2 record is via the portal. If necessary, there's a field validation rule that can be used to catch and prevent duplicates you can use.

              • 4. Re: Adding automated text to a user entered value?
                productionQC

                     If it helps, my current script for the button is:

                     Go to Field [TESTS::TestNo.]
                     Got to Portal Row [Last]
                     Set Field [TESTS:TestNo.]
                     Go to Field [TESTS::TestNo.]
                     Got to Portal Row [Last]
                     Set Field [TESTS:TestNo.]
                     Go to Field [TESTS::TestNo.]
                     Got to Portal Row [Last]
                     Set Field [TESTS:TestNo.]
                     Go to Field [TESTS::TestNo.]
                     Got to Portal Row [Last]
                     Set Field [TESTS:TestNo.]

                           

                     TesNo. field is populted via it's relationship to table 1

                     Thanks

                      

                • 5. Re: Adding automated text to a user entered value?
                  philmodjunk

                       I try to avoid interacting with a portal this way. This type of script can "break" pretty easily should the layout's design be modified at some point in the future. I prefer to have the script freeze the window and then change layouts to a layout based on the portal table with the PrimaryKey value placed in a variable where the script can use New Record/Request in a loop to create the needed records, then changes back to the original layout.

                       But be careful of script triggers that may be tripped by such a method when the script changes layouts.

                  • 6. Re: Adding automated text to a user entered value?
                    productionQC

                         The numbers have to be entered by the user.  They cannot be gereated.  The numbers are pre-printed our their existing forms in the lab, so one user may enter 12345 and the next might have a booklet that is 34531.  All we can do is add the letters to the end as this is thier current setup and thy have thousands of test done this way, that they want entered.  So on table 1 the user inputs the number off their sheet and then the test numbers in table two become based on it, but adding the TA, TB etc to the end.  Using my script above, is there a way to add to it to make the letters sequentially enter after each number.  So everytime it starts at "TA" then on the second one it looks up to see "TA" has been used and thusly put in "TB" and so on.  Then when a new record is generated on table 1 and the scripted button is pushed again the portal does the same thing all over again.   There will probably be no more than four test per record in table 1, sometime they may add more, but rarely.  This is why I have the whole alphabet in a dropdown.  Every single record in table 2 does not need an original letter, as they have original numbers entered by the user in table one.  Therefore each record in table one will have 4 (or more) related ones in table two with same number, but letters added.  So if you were to look at a report or whatever, it would read:

                         Product 12345 Results:

                         Test 12345TA
                         Test 12345TB
                         Test 12345TC
                         Test 12345TD

                         Product 34512 Results:

                         Test 34512TA
                         Test 34512TB
                         Test 34512TC
                         Test 34512TD

                         ETC ETC ETC...

                          

                    • 7. Re: Adding automated text to a user entered value?
                      productionQC

                           Ok, I agree with what you just said.  So if I make the said changes to the portal interaction, I would still need your help creating the script to loop for the desired number of entries as well as how to connect the letters.

                           Secondly, I am assuming when the script takes us back to the layout on table 1 that the portal would then show the recently added related records, all we are doing is shifting the entry point to the layout on table 2, rather than via the portal, correct.

                           Thanks

                            

                      • 8. Re: Adding automated text to a user entered value?
                        philmodjunk
                             

                                  Using my script above, is there a way to add to it to make the letters sequentially enter after each number.

                             The auto-enter calculation that I mentioned should do that automatically as each new record is created.

                             

                                  Every single record in table 2 does not need an original letter, as they have original numbers entered by the user in table one.  Therefore each record in table one will have 4 (or more) related ones in table two with same number, but letters added.  So if you were to look at a report or whatever, it would read:

                             That was my understanding when I suggested a solution.

                             But I'd set up the script for creating related records in table 2 like this:

                             #From the Table 1 based layout:
                             Set Variable [$ID ; Table1::PrimaryKey]--I'd use an auto-entered serial number independent from the user entered ID number
                             Freeze Window
                             Go to Layout [Table 2]
                             Loop
                                Set Variable [$K ; value: $K + 1]
                                New Record/Request
                                Set Field [Table2::ForeignKey ; $ID]
                                Exit Loop if [$K > 4 ]
                             End Loop
                             Go to Layout [original layout]

                             The reason that I'd use the auto-entered serial number as the primary key and the field used for this relationship instead of the user entered value is that people make mistakes when entering data. If they enter and ID number, edit date in the related records in table 2, then discover their data entry error, fixing that error without losing the test record data in table 2 can be slightly complicted. If the user enters that value only in a field in Table 1, they can just edit that field to correct the error. And your calculation field that combines this value with the sequencing letter can refer to the field in Table 1--it doesn't have to refer to a field in table 2.

                        • 9. Re: Adding automated text to a user entered value?
                          productionQC

                               Perfect, ok I understand the loop you have created, fantastic.  That will work like a charm.  But I am still not wrapping my head around the autoentry part for the letters.  So to understand is it just taking the letter "A" and essentialy adding 1 to it based on the char code.  Almost like the old ASCII key codes?  

                               If I use the script you posted above to genereate the loop, at what point do I add the auto-enter?

                                

                               

                                    Your calculation that combines the foreign key (123456) with the text could then be:

                               

                                    ForeignKey & "T" and Char ( Code ( "A" ) + seq - 1 )

                               

                                    You can then use a self join relationship and an auto-enter calculation to assign values to seq in each record.

                               

                                    The self join relationship:

                               

                                    Table 2::ForeignKey = Table2SameKey::ForeignKey

                               

                                    The auto-enter Calculation:

                               

                                    Max ( Table2SameKey::Seq ) + 1

                               

                                    clear the "do not evaluate if all referenced fields are empty" option in the specify calculation dialog.

                               

                                    clear the "do not replace existing value..." field option.

                               Not very clear on this part, getting more advanced, I am sure I can do it, but perhaps you could nudge me a little further in the right direction. 

                               Thanks

                                

                          • 10. Re: Adding automated text to a user entered value?
                            philmodjunk

                                 Auto-enter is a calculation you specify in Field options. Find the field definition in manage | Database | Fields, double click it, click the auto-enter tab and select the "calculation" option. This is why this setting works for the script and also when the user adds additional records in table 2.

                                 So to understand is it just taking the letter "A" and essentialy adding 1 to it based on the char code.  Almost like the old ASCII key codes?

                                 That's exactly right, it's just using Unicode (I think) instead of Ascii. You can also determine the value of Code ( "A" ) and use that constant in place of that function call if you want. I find Code ( "A" ) a "self documenting" way to get the same result. I suggested this method so that we could use the Max function--which requires a number, to produce the next letter in the sequence for each new record.