I have a form that kicks off a workflow and has cascading dropdowns to narrow down the selections and ultimately provide one transfer code.
Business Unit > Debit Account Name/Number > Beneficiary Account Name/Number > Transfer Code
Business Unit A has X number of Debit Accounts and each Debit Account has X number of Beneficiary Accounts, but each Beneficiary Account has only one corresponding Transfer Code. "Business Unit", "Debit Account Name/Number", and "Beneficiary Account Name/Number" are all List Lookup controls and "Transfer Code" is a Calculated Value control.
My cascading fields work perfectly up until the Transfer Code. The Transfer Code should be auto-populated based on whichever Beneficiary Account Name/Number the user selected. We tested the formula by hardcoding data and the formula works just fine. The "Beneficiary Account Name/Number" that is used in the lookup is a single line of text field in a separate list called "Transfer Code List". This data gets uploaded using an Excel spreadsheet that is retrieved from the bank. We format the data in the spreadsheet and combine the "Beneficiary Account Name" and "Beneficiary Account Number" fields into a new field and then paste the data into the list. (Kind of a lot of data massaging going on here, but we can't get a usable format from the bank, so what are you going to do?)
My formula in the Calculated Value control:
lookup("Transfer Code List","Beneficiary Account Name/Number",BenAcctNameNum,"TransferCode")
I am telling the control to look at the Transfer Code List, filter on the Beneficiary Account Name/Number column, and if it matches the data that was selected in the BenAcctNameNum control in the previous step, return the corresponding Transfer Code. It never worked, so we looked at the data that was selected in the "BenAcctNameNum" control and saw that it does not return clean data (Big Business - 123456). Instead, it throws in the item ID and separates it with a semicolon and pound (5555;#Big Business - 123456). As a result, my Calculated Field does not work because the data returned in the "BenAcctNameNum" control does not match the data stored in the "Beneficiary Account Name/Number" column in the list.
How do I get rid of the ID;# combination so my List Lookup will return only clean data that matches? When I look at the data in Excel, it does not show the ID;# combination, nor is it there when I export it back out of SharePoint.