Skip to main content

Search

Extract part value of another column - I have a column on my form that has two values concatenated and I want to extract part of that value

Comments

13 comments

  • Ardevia Forum

    Original Post by Andrii Katsiubka (Imported from Ardevia Forum)

    Try to set next calculated expression on Account field

    Code:

     

    var accountCode = [[<Account No Name field name>]];
    return accountCode ? accountCode.split('-')[0].trim() : accountCode;
    

     

    Replace <Account No Name field name> by field name  which holds an account number followed by an account name and set "Function code" expression type for this expression

    0
  • Ardevia Forum

    Original Post by Sandy (Imported from Ardevia Forum)

    Hi Andrii,

     

    I have implemented this code where I have an action on the Save button to update another column in the list and I am getting this error:

     

    Failed evaluate property "Account" in "Update Petty Cash Voucher Lines":Error occured on evaluating expression "{ var accountCode = [[@SubLists.Details.Account_x0020_No_x0020_Name]]; return accountCode ? accountCode.split('-')[0].trim() : accountCode; }"
    accountCode.split is not a function

     

    The expression function code that I have inserted there is as follows:

     

      var accountCode = [[@SubLists.Details.Account_x0020_No_x0020_Name]];
    return accountCode ? accountCode.split('-')[0].trim() : accountCode;

     


    Hoping you can help.

     

    Thanks
    Sandy

    0
  • Ardevia Forum

    Original Post by Andrii Katsiubka (Imported from Ardevia Forum)

    Hi Sandy,

     

    Can you describe more detail which lists do you have and what and where you want to update?
    Because in last post you use sublist pleaceholder in expression.

    0
  • Ardevia Forum

    Original Post by Sandy (Imported from Ardevia Forum)

    Hi Andrii,

     

    I have a primary list called Petty Cash Voucher and a sub-list called Petty Cash Voucher Line.

     

    When I am performing the update it is an Update List Item action on the Save button.

     

    In Sublist, I have two columns related to this function, an ID and other columns not related to this issue:

     

    Account  (Account)
    Account No Name (Account_x0020_No_x0020_Name)

     

    The value stored in Account No Name is e.g. 10020 - Miscellaneous Fees which displays on the form nicely for the user to fill out the form, however, I need to extract the digits that appear before the '-' and store this value in the Account column. This is necessary as I am exporting this information to Excel for import into a Finance system.

     

    On the Account column, I have the previously supplied expression.

     

    Hope this helps.

     

    Thanks
    Sandy

    0
  • Ardevia Forum

    Original Post by Andrii Katsiubka (Imported from Ardevia Forum)

    You don't need "Update List Item" action to achieve the desired result.

    Try following:

    • Set calculated expression on sublist for Account:
      • Select cell under Account column
      • Open Behaviour tab and open expression builder for Calculated expression
      • Select "Function Code" expression type and insert exression below:

    Code:

     

    var accountCode = [[@SubListCurrentRow.<Name of sublist>_.Account_x0020_No_x0020_Name]];
    return accountCode ? accountCode.split('-')[0].trim() : accountCode;
    

     

    • Also define calculated expression for Account field on New and Edit Forms of  "Petty Cash Voucher Line" list
    • Code:

     

    var accountCode = [[Account_x0020_No_x0020_Name]];
    return accountCode ? accountCode.split('-')[0].trim() : accountCode;
    
    0
  • Ardevia Forum

    Original Post by Sandy (Imported from Ardevia Forum)

    Hi,

     

    How does this work when the Account column is not visible in the sub-list default view?

     

    Thanks
    Sandy

    0
  • Ardevia Forum

    Original Post by Andrii Berezovskyi (Imported from Ardevia Forum)

    This will not work when the account columns is not visible, but at first you can make it visible and after evaluating expression make it not visible using css.

     

    Best Regards
    Andrii Berezovskiy

    0
  • Ardevia Forum

    Original Post by Sandy (Imported from Ardevia Forum)

    Hi,

     

    I have tried the suggestions above and still no luck, I am still getting the same error accountCode.split is not a function.

     

    I have made the sublist column visible and added the calcualted expression as suggested but still getting an error.

     

    Sandy

    0
  • Ardevia Forum

    Original Post by Markiyan (Imported from Ardevia Forum)

    Hi

     

    Can you please provide us with more details, so we will be able to reproduce your issue. It would be good if you will send the screenshots.

    0
  • Ardevia Forum

    Original Post by Sandy (Imported from Ardevia Forum)

    Hi Markiyan,

     

    I have a few screenshots here of a similar thing I am trying to get to work....

     

    Here is the sublist column I am trying to split.

     

    Here is the sublist how it appears on the form.

     

    Here is the Account Column calculated expression .

     

    Here is the error  I get on the form when I select the Natural Account.

     

    In the previous response, there was reference to setting up a calculated expression for the sublist column on the add and edit forms, but I don't need to set up these forms as the list form would never be used independently.

     

    KR
    Sandy

    0
  • Ardevia Forum

    Original Post by iryna (Imported from Ardevia Forum)

    You need to have string to use split() method:
    var accountCode=[[...]].toString()
    It will return something like: 1;#12030-Marketing
    where 1;# is an option number

    0
  • Ardevia Forum

    Original Post by Sandy (Imported from Ardevia Forum)

    Hi,

     

    With reference to the sub-list columns that are visible, how would I make them not visible using css.

     

    The user can enter multiple rows in the sub-list and for each row I need to calculate a value based on the content of other fields in the row.

     

    eg.

     

    I have a column that needs to be calculated by concatenating several columns together but I don't want to show this column on the form at all.

     

    Thanks

    Sandy

    0
  • Ardevia Forum

    Original Post by Vitaliy Zadorozhnyy (Imported from Ardevia Forum)

    Hi Sandy,
    We would recommend just not add this field to sub list view and update it by using "Update List Item" custom action on save button after save form action and before redirect action. Because it is not easy to hide the column in sub list as we use usual list view webpart just in grid mode and on creating any new row SharePoint will create this column anyway.

     

    In the List Item Id of the update custom action you can put several ids separated by comma, to get just created sub list items you can use ardevia context function .

    Code:

     

     [[@Web.GetValuesForQuery('Sub List Url', 'Caml query', 'ID')]]
    

     

    CAML query can for example describe condition: "Get last N items created by current user in sublist".
    items count you can get using placeholder:

    Code:

     

    [[@SubLists.YourSublist.SomeNotNullableField.Count]]
    

     

    Hope this helps.

    0

Please sign in to leave a comment.