Concatenating Columns When Calculated Column Is Unavailable: Alternative Approaches

Description:

Creating Calculated Columns for Concatenation in Evolve Data References

During the migration process from SharePoint List to Evolve Data References, the calculated columns were unfortunately lost. However, there are alternative methods to recreate these calculated columns. In the Foundation solution, you previously created some SharePoint lists with Winshuttle queries.

To replicate the functionality of the lost calculated columns, follow these steps:

After the initial extraction in Evolve, access the SP list settings.
Add a new calculated column that concatenates the Key value and Description value.
This newly created column will be utilized in a dropdown list to retrieve a key based on either the key value or its description.

By implementing this approach, you can achieve similar functionality to the previous calculated columns that were present in the SharePoint List during the migration process to Evolve Data References.

Works with Evolve 20.0, 20.1

Cause:

Calculated column is not available in Evolve

Solution:

Concatenating Reference Data

The solution can be easily replicated by adding an extra step to concatenate the reference data. Follow these steps:

Basic Steps:

Retrieve data from Reference Data in a repeating table.
Update the reference data with the concatenated value.
Retrieve that value in a dropdown.

Detailed Steps:

Create a Reference Data.
In the Solution, create a Data Connection using the Reference Data.
In the Form, add two groups consisting of a text box, a repeating table, and two buttons each.
In the first group, name the text box “Data Log,” the repeating table “OutputData,” and the buttons “Get Reference Data” and “Calculate Data.”
In the second group, name the text box “Data Log1,” the repeating table “InputData,” and the buttons “Copy Data” and “Update Reference Data.”
Add two Web Service Elements.
On the Solution tab, right-click on the created Data Connection and create Field Mapping.
Provide the required names and select “Get” from the Operation Dropdown, then click “Next.”

By following these steps, you can replicate the solution and include the additional step of concatenating the reference data.

9. Again right click on the Data Connection and Create Field Mapping but this time in Operation Dropdown, select Save and click on Next

10. Associate the Webservice Elements with the GetData and SaveData webservices that we created through the field mapping in Data Connection and Get Reference Data and Update Reference Data Button respectively

11. On the Calculate Data Button, add a custom rule.

For 20.0

var repCount = fxcount(‘/my:myFields/my:Grp/my:Grp_Output/my:OutputData/my:Repeating_Content/my:Int’);

for(var i = 0; i < repCount; i++)
{
$form.setValue(‘/my:myFields/my:Grp/my:Grp_Output/my:OutputData/my:Repeating_Content[‘ + i + ‘]/my:UserName’,concat($form.getValue(‘/my:myFields/my:Grp/my:Grp_Output/my:OutputData/my:Repeating_Content[‘ + i + ‘]/my:Int’), ‘-username’));
}

For 20.1

var repCount = count(‘/my:myFields/my:Grp/my:Grp_Output/my:OutputData/my:Repeating_Content/my:Int’);

12. On the Copy Data Button add Copy Table Data Action Rule. In Primary Source put the OutputData Repeating table and in Target put InputData Repeating table