3 Replies Latest reply on Sep 26, 2012 12:16 PM by philmodjunk

    Automatic look up field



      Automatic look up field


      Help with Checkbox Value List English to Spanish automation


           Hey PhilModJunk! I tried to set this up (of course changing the names to protect the innocent)... I can't get it to work. I got to the end and when I entered into my checkbox set, the second field did not input the corresponding value, it enters only the same value no matter what box is checked in the first field and across different records.  I thought I went through all the steps methodically but I must have missed something somewhere. Did I need ot enable something in the relationship graph? The link above is the original thread and the Text below is the copy of the pertinent paragraph. I have a list of Procedures with descriptions and a code for each procedure. I would like to be able to click on the procedure decription performed and have it autoenter the appropriate code in another field.

           The person had a report card with check boxes of comments in English and wanted to have the translated Spanish comment be checked off automatically.

           "Define a table named Comments where you have two fields: English, Spanish.

                          Create one record for each comment and put the English version in the English field and the Spanish version in the Spanish field.

                           Use Edit Value lists to create two value lists for your two fields. Use the Specify vales from a field option to list the values from English for the first Comments field and set up the second value list to list values from the Spanish comments field.

            Link it in a relationship similar to this:

            OriginalTable::Comments = Comments::English

           Define a text field, formatted with the Spanish value list to auto-enter the data in Comments::Spanish using this calculation:

           List ( Comments::Spanish )

           Clear the "do not replace existing value" check box.

           When you select an English Lanaguage comment, the second comment field will automatically look up the corresponding Spanish comment and enter it into the field.

           I can send screen shots if necessary, I thought you might know of a common thing missed that would cause the same value to be entered repeatedly.




        • 1. Re: Automatic look up field

                    I have a list of Procedures with descriptions and a code for each procedure. I would like to be able to click on the procedure decription performed and have it autoenter the appropriate code in another field.

               This does not require the rather elaborate solution we came up with in that thread where the user had two different fields formatted with a check box group and had to "synch" the selections made in one to match the selections made in the other.

               In your case, it would seem that all you need is a table with at least two fields:

               ProcedureNumber, ProcedureDescription

               There are then a number of ways all based on a relationship matching to either ProcedureNumber or ProcedureDescription that would seem to do what you want. The structure of your tables and the design of your layout--details missing from your original post will help determine which exact option will be easiest to implement.

               A particularly important detail is whether you will be selecting one procedure or multiple procedures.

               A basic method for doing this is to establish a drop down list of ProcedureDescriptions and then a relationship based on ProcedureDescription can either directly reference or use a looked up value field option to make a copy of the ProcedureNumber corresponding with that procedure.

               But keep in mind that this is not the only option.

          • 2. Re: Automatic look up field

                 Thank you.

                 I think the issue was related to the ascending sort, I started over and step by step recreated everything and did get it to work when I did get it to work I added a 3rd field for the fee schedule and also added "a" "b"  "c" to the descriptions.. The fee schedule was displayed albeit out of order, but when I checked the description box the appropriate corresponding fee was checked as well even though it didn't line up. The fee shedule is a text field at present, I will see what happens if I change it to a number field... and I will also play with the "invisible key" format you suggested later on in that post.

                 And thank you for pointing out the "multiple". I will give some thought to that aspect and try out a few different configurations. This will now save me several steps with my billing process.

                 Thanks again!


            • 3. Re: Automatic look up field

                   When selecing multiple procedures, use a portal to a related table such that each selected procedure creates a new related record in the portal. This setup will be much more flexible and easier to maintain than what you have now.