6 Replies Latest reply on Feb 12, 2014 11:52 AM by yomango

    Choosing fields from Tables for concatenation

    yomango

      Title

      Choosing fields from Tables for concatenation

      Post

           Hello.Is there a way to have a third table made of fields I select from two or more tables? In my case, I have two tables with several fields on each one. I want to be able to choose any field(s) from those tables so they show in a concatenated field on a third table. Sometimes I choose five fields from one table and three fields from the other one, sometimes I choose other differently. I was think to do that in a script but can't get my mind to engage on how to do it. Thank you.

        • 1. Re: Choosing fields from Tables for concatenation
          philmodjunk

               and what means do you imagine using to "choose" the fields?

          • 2. Re: Choosing fields from Tables for concatenation
            yomango

                 Thank you for asking.

                 If I go to table 1/ field1  and want to concatenate it, in table 3, to table 1/ field 2, etc, then I go to table 2/ field 3 and want to concatenate it to fields 1 and fields 2 from table 1, etc. How to do it. Each time I may choose/pick different fields from the two tables to have one concatenated field in table 3. Do I make sense?

                 I am working it now by having a fourth table (table 4) with 50 text fields as a "temporary" or "transition" table where, through a script I copy the contents of a field from one of the two tables into the first empty field on the temporary table. I am hoping then to be able to concatenate them into one single field. Does it make sense? My doubts here are,  if I want to choose the content of more than 50 fields I'd have to create more fields in the temporary table and recalculate the concatenated field. 

            • 3. Re: Choosing fields from Tables for concatenation
              philmodjunk

                   So are you clicking each field to select it? And the order in which you click the fields determines the order in which they will be concatenated?

                   And what relationship, if any, are you using between the two tables? If you have fields in two different tables, you need also to select the record as well as the field in each case.

                   Could you provide an example with actual field values of what you are trying to produce?

                   I think I see a way to do what you want with a script and the evaluate function, but the devil is in these details.

              • 4. Re: Choosing fields from Tables for concatenation
                yomango

                     Yes. I am clicking on the fields I want them to go in the concatenated field (in another table). But first,with the help of a script I send each field to a transition table with 50 empty fields. Each field information copied from the tables takes the next available empty field. when I am through clicking on the fields I want from two tables, the concatenated field in a separate table gets populated. I use buttons to start the concatenation by creating a new record on the transition table, another button jumps to the next table where the other fields to be clicked  are and a button to create the final concatenation. I am attaching the file for your review. It is working now that I have a few fields on each table (Products Text and country text). The way I figure, I have a wood burner instead of an electric one, but it works. My question is what if I have to choose from more than 50 fields.Thank you for your time.

                • 5. Re: Choosing fields from Tables for concatenation
                  philmodjunk

                       So I will assume the answer to this question:

                       

                            And the order in which you click the fields determines the order in which they will be concatenated?

                       is "Yes".

                       A single script can concatenate the field content as you click each field or a button associated with each field. Use the script parameter option to pass the contents of the selected field to the script and you get a very simple script:

                       Set Variable [$$Concat ; value: $Concat & " " & Get ( ScriptParameter ) ]

                       Then you can click an additional button to "save" the concatenated data in your other table if that's still what you want to do:

                       Freeze Window
                       Set Variable [$Primary Key ; value: YourTable::PrimaryKey ]
                       Go to Layout ["Transition" (Transition)]
                       New Record/Request
                       Set Field [ Transition::ForeignKey ; $Primarykey]
                       Set Field [Transition::Concatenated ; $$Concat ]
                       Set Variable [$$Concat ; value: "" ]
                       Go to Layout [original layout]

                  • 6. Re: Choosing fields from Tables for concatenation
                    yomango

                         Thank you, Phil. as always I thank heaven for having you in this forum. Excellent.