7 Replies Latest reply: May 15, 2014 10:07 AM by Data Kruncher RSS

    How to get textline user-defined function output to appear in table?

    dgr _

      hello,

      I'm newly-learning today how to use user-defined functions and the TextLine function specifically to try to capture a specific type of row in a report that variably-repeats and then output fields from this type of row into their own columns.

       

      For example in the variable-repeating row

      (FYI these rows can occur as little as zero times and up to a few times...I used the Cleared By... Template feature to handle that type of repeating, by clearing the variable template with one that occurs for every account given in the report, my Table output in that sense looks good) 

      there's a field named

       

      Ins Name

       

      and I made a User-Defined Function

       

      Ins_Name_1

       

      with a formula of

       

      TextLine(,1)

       

      with the desired result to be the formula captures the first Ins Name occurance in a group of repeating rows of that type if there happens to be at least one occurance of Ins Name for a given account in the report

       

      and I then made a second User-Defined Function

      Ins_Name_2

      with a formula of

      TextLine(,2)

      with the desired result to be the formula captures the second Ins Name occurance in a group of repeating rows of that type if there happens to be a second occurance of Ins Name for a given account in the report

       

      Monarch Pro 10.5 seems to have no problem with the above.

       

      I then go into Table view, right click in an empty area after the last captured column of data, select

      New Calculated Field.....

      Formula Based

      set the General tab options,

      go to the Formula tab

      select my User-Defined Function

      INS_NAME_1()

      and it appears in the Expression: area, and it's highlighted in Blue.

      I then click OK

       

      and I get the Error of

      Operand Count.

       

      this is where I'm stuck and is why I'm making this forum post.

       

      What else do I need to do and/or what did  I do wrong above to be able to get the desired two new "calculated" columns to appear in my Table?

       

      Thanks in advance,

      David

        • How to get textline user-defined function output to appear in table?
          Data Kruncher

          Hi David,

           

          You're off to a great start, and you're really close to your solution.

           

          The operand count error is not-so-clearly trying to tell you that you  were supposed to supply a parameter for the function that you're trying  to use.

           

          Your UDF named Ins_Name_1 requires that you give it a value to work with using a calculated field.

           

          In your UDF definition, you probably want to make the function more flexible by defining a parameter to pass to your function.

           

          In the UDF dialog, click the Add button and enter MultiLineField as the parameter name, Character type, then in the definition formula, use TextLine(MultiLineField,1).

           

          Now for the calculated field, call the new function by building your formula using Ins_Name_1().

           

          Questions?

           

          Kruncher

            • How to get textline user-defined function output to appear in table?
              dgr _

              Data Kruncher,

              Thank you for the quick & helpful reply.

              I followed your instructions...and now am getting a new errir in the Field Properties>Formula window after entering in the Expression as you stated of

              Ins_Name_1()

               

              The error I get after clicking OK is

               

              Maximum record length exceeded.

               

              What do I do next? 

              Could this have anything to do with the fact that on some accounts in the report this variable row of Insurance information I'm trying to capture sometimes doesn't occur at all?  Right now in the Table view for those times that it doesn't, those columns of the table are correctly blank.  And I'm able at present to successfully capture the first row of Insurance information if there is that provided for an acocunt.  It's just that I can't (yet) capture the second, third, etc. rows of Insurance information if the account in question happens to have one.

               

              Thanks!

                • How to get textline user-defined function output to appear in table?
                  Data Kruncher

                  One of Monarch's limitations is that the total number of characters that appear for the values displayed in all of the fields within a single record cannot exceed 4,000 characters.

                   

                  You'll need to hide one or more fields to get back under the 4,000 character limit. Hiding your INS_NAME field may do the trick as you're splitting it up and duplicating it.

                    • How to get textline user-defined function output to appear in table?
                      dgr _

                      Data Kruncher,

                      Hello,

                      In Table view, I right-clicked and Hid the INS NAME field so that it's now appearing the Hidden Fields: window.

                      I then re-did the steps to make the

                      New Calculated Field.....

                      Formula Based

                      and the same

                      Maximum record length exceeded.

                      error occurred.

                       

                      What do I do next?

                        • How to get textline user-defined function output to appear in table?
                          Data Kruncher

                          Hmm... tough to tell without seeing the table.

                           

                          How many fields are displayed, and do you have any Memo type fields, or are all of the text fields only Character type?

                           

                          In my experience, that error is pretty specific to the entire record, and not just due to adding a single calculated field (unless of course that one field pushes the character count over the threshold).

                           

                          Can you post a brief sample of your Table window, with the data scrubbed to be gibberish but representative of the structure? Or maybe a few screenshots uploaded to a free service like Photobucket.com?

                           

                          Anyone else have any ideas for David at this point?

                            • How to get textline user-defined function output to appear in table?
                              dgr _

                              Data Kruncher,

                              Hello,

                              Well I got the Error to go away by (1) using a much smaller test data input file with about 24 accounts instead of over 400 and removed all but one "main" and a "insurance" Template to help troubleshoot (2) In the field properties for all the "variably occurring" Insurance fields I changed the End Field On from None of the above to Line Count 1.  When looking at the Report view after making this change the highlighted/captured data changed from being obviously incorrect to only highlighting the variably-occurring Insurance rows when the occurred and none of the other not-related rows

                               

                              I also Hid every column of Insurance data from the Table output.

                               

                              I'm now at the point where the Table output is only displaying the Account data that occurs once per account and every time per account and then I made two Calculated Fields InsuranceName1 and InsuranceName2 that are supposed to pull the first and second occurrence of INS NAME for an account if that account happens to have two Insurance occurrence rows.

                               

                              The problem at the moment is that in the Table Output the InsuranceName1 and InsuranceName2 is not in it's correct locations.  Let me illustrate:

                               

                               

                              the currently-erroneous output

                               

                              acctno    other  data            InsuranceName1   InsuranceName2

                              -


                              acct123   otherdata123                medicare          (blank)

                              acct456   otherdata456                bcbs                (blank)

                               

                              bcbs should've been in acct123's rows and the InsuranceName2 column....but instead it's in acct456's row and InsuranceName1's column

                              In the data file I'm reading from acct123 has two lines of Insurance information, medicare is the name on line one, bcbs is the Name on line 2  and for acct456 there is no lines of insurance information given.

                               

                               

                               

                              how the output should look if the calculated fields etc. were working properly

                               

                              acctno    other  data            InsuranceName1   InsuranceName2

                              -


                              acct123   otherdata123                medicare            bcbs

                              acct456   otherdata456               (blank)                (blank)

                               

                               

                              what do I do next?

                               

                              thanks,

                              david