10 Replies Latest reply on Jan 16, 2020 2:04 AM by Cosmin Orhean

    How to concatenate column values from Table List into a character field?

    Cosmin Orhean
      Share This:

      Hello everyone,

       

      I'm trying to concatenate the column values of a Table List into a character field by using an Active Link Guide looping through all the rows of the Table List.

      Here is a graphic representation of my scenario:

      AL1 with Execution order = 100 is populating the "InstanceID" field  using Set Fields. After the Set Fields action there is a Change Field action refreshing the Table List.

      AL2 with Execution Order = 120 is calling the guide.

      AL1 and AL2 are triggered by the same button.

      For some reason the "name" field remains blank after the guide fires AL3 for each row of the Table List.

      Looking at the AL logs, it seems that the AL3 executes the Set Fields action each time, but each time it does nothing. Usually you see in the logs the values that the Set Fields actions are writing in the destination fields, but in my case there is nothing next to the Set Fields action.

       

      What am I doing wrong? Can anyone give me some advice?

       

      Thank you,

      Cosmin

        • 1. Re: How to concatenate column values from Table List into a character field?
          rodrigo barcat

          After set fields, are you using a commit? How do you record the register on the table?

          In the table loop you set to read all rows? And in your table do you have a condition to decrease the number of lines after the record is saved on the table?

          • 2. Re: How to concatenate column values from Table List into a character field?
            Sinisa Mikor

            Hello Cosmin,

             

            if you are certain that table is being populated and that AL3 activates for each record, but nothing, not even a single semi-colon, is written into $name$, maybe user lacks write permission to that field?  Everything else you've described should produce results you're looking for;  calling active link guide can be moved to the end of AL1 if you wish to reduce number of active links involved.

            1 of 1 people found this helpful
            • 3. Re: How to concatenate column values from Table List into a character field?
              Cosmin Orhean

              Hi Rodrigo,

               

              I'll try to answer to your questions:

               

              Q1: "After set fields, are you using a commit?"

                 I'm not using commit because I have a display only form, therefore no DB table.

               

              Q2: "How do you record the register on the table?"

                 I don't understand the question or how and why I must do that.

               

              Q3: "In the table loop you set to read all rows?"

                Yes, the AL2 calling the guide is set to Loop through all rows

               

              Q4: "And in your table do you have a condition to decrease the number of lines after the record is saved on the table?"

              I'm not sure how I could do that. I only have the qualification $InstanceID$ = 'ID'

              Regarding the record saving, I'm using only display fields, therefore no saving.

               

              Thank you,

              Cosmin

              • 4. Re: How to concatenate column values from Table List into a character field?
                Sinisa Mikor

                I've tested various combinations of Run If Qualifications of AL3 and (lack of) permissions of name and Column fields and only two failed to do what they were supposed to without reporting error; in both cases, Columns field had no permission at all so it didn't show up in the table -- if Run If Qualification was defined as in your example, AL3 would be steered to False actions (which would leave name in its original state, thus avoiding need for permission check),  but if Column was removed from Run If Qualification and all permissions removed from name field, Set Fields action would silently ignore Column field in expression and only append semi-colon character to value of name from previous iteration (despite lacking permission to do so), and active links log would display those actions.

                • 5. Re: How to concatenate column values from Table List into a character field?
                  Phillip Brockhaus

                  If there's nothing proprietary, could you upload a def file including the display only form, the join form the table field points to and the forms behind that, your guide, and the three active links?

                   

                  If it's easier, try to reproduce the problem on a new generic dummy form with a simple back-end form for the table field. If it works on the new forms, try to figure out what's different between the two. If it doesn't work, you can upload this simplified version that does not contain any proprietary information.

                   

                  I'm sure we will be able to spot the problem right away if we look at the code.

                   

                  I agree with Sinisa Mikor that it sounds like a permissions problem.

                  • 6. Re: How to concatenate column values from Table List into a character field?
                    Cosmin Orhean

                    Hi Sinisa,

                     

                    The values are visible in the Table List and the user has permission on all objects involved.

                     

                    I believe I have narrowed the investigation a little bit by performing the following tests:

                     

                    TEST 1:

                    I modified the AL3 Set Fields as follows: 'name' = $Column$

                    In this scenario the "name" field from the display only form receives the value of the last row in the table.

                    Looking at the AL logs, I see the values applied by the AL3 after each iterration.

                     

                    TEST 2:

                    I modified the AL3 Set Fields as follows: 'name' = $Hostname$, where "Hostname" is another field from the same display only form that receives a value from a previous AL.

                    In this scenario the "name" field remains blank. It behaves the same if I use 'name' = $name$

                    Looking at the AL logs, in each iteration of AL3 there is nothing after Set Fields:

                     

                    My assumption is that, although the display only field values can be used in qualifications, the values cannot be applied to fields in the Set Fields actions.

                    Am I right? If yes, does anyone know a way to achieve my goal?

                     

                    Thank you all for contributing to this investigation!

                     

                    Cosmin

                    • 7. Re: How to concatenate column values from Table List into a character field?
                      Cosmin Orhean

                      Hi Phillip,

                       

                      It would be better to reproduce the problem on a new generic dummy form than exporting the original form which is dependent on other workflows, but I believe my last tests have narrowed the investigation focus. What do you think about my assumption?

                       

                      Thank you,

                      Cosmin

                      • 8. Re: How to concatenate column values from Table List into a character field?
                        Sinisa Mikor

                        You may be partly right. Just to be sure as to what happens, I've created display-only character field on my test form and it behaves just as optional or required fields do, meaning that value from previous iteration is available.  The only thing different from your setup is the form itself -- while you're using display only form, I've been testing on a regular one.  Would you test what happens in your installation if regular form is used instead?  (If that one behaves as supposed to, maybe try creating another display-only form and repeating tests to rule out the possibility that original form was somehow damaged.)

                        • 9. Re: How to concatenate column values from Table List into a character field?
                          Fred Grooms

                          Try changing your AL3 from a single set fields action to 2

                          Action 1

                             Set  zNewTmpChar = $Column$

                          Action 2

                             Set Name = $Name$ + zNewTmpChar + ";"

                           

                          I have seen it before (a long time ago) where the system has trouble appending using a column directly.  Putting the column data into a separate display only temp field and using that to do the append always works

                          3 of 3 people found this helpful
                          • 10. Re: How to concatenate column values from Table List into a character field?
                            Cosmin Orhean

                            Hi everyone,

                             

                            I had to put on hold this topic, but as soon as my agenda clears I'll doo more tests and get back with the results. Thank you all for your help so far.

                             

                            Have a great day,

                            Cosmin