Let’s Revisit How to use both the Selected and the Edited records in a Datatable

A year ago, I showed you how you can use a Flow with a Loop and a special Apex action to update the Selected records from the Datatable component with the Edited records from the same Datatable.

Now I’m going to show you how you can get rid of the Loop and use a different action and a simple assignment to produce the same results.

My Datatable Flow Screen Component allows a user to both Select records and make Edits to records.  The component returns two separate collection variables.  One of them includes the original values of just the Selected records.  The other one includes just the Edited records whether they were selected or not.

Sometimes, you may want to process just the selected records in your flow, but include the edited values in those selected records.  This sample flow shows how you can create a selected record collection with edits.  The flow uses the Get Common and Uncommon Records action that is part of the Collection Actions from UnofficialSF.com to compare the collections returned by the Datatable and extract the common and unique records from them.  

Here’s a Datatable displaying all of the Product records from an Opportunity.

I’ll select 3 records and make edits to 2 of them.

My normal outputs from the Datatable include the original 3 selected records and a separate collection of just the edited records.

To create a record collection that combines these two requires just a couple extra steps in your flow.  The magic happens in the Get Common and Uncommon Records Flow Action.  This action takes two separate record collections, Source (Selected) and Target (Edited), along with a field from each (Id) that is used to match them up and returns 4 separate record collections.

  • Source Common – Records from the Source collection that are also in the Target collection
  • Source Unique – Records from the Source collection that are not in the Target collection
  • Target Common – Records from the Target collection that are also in the Source collection
  • Target Unique – Records from the Target collection that are not in the Source collection

First, I created a Record Collection variable for Opportunity Product that will be used to store the combined outputs from the Action.

I pass the outputs from the Datatable into the Get Common and Uncommon Records Action.

We are only going to use two of the output collections and they will be combined into a single collection of Selected records that include any of the Edits made to them.

  • Source Unique – All Selected records that are not also Edited records
  • Target Common – All Edited records that are also Selected records

An Assignment node is used to Add each of the desired collection outputs from the Action into our final record collection of selected and edited records.


As you can see in the Results Screen, the Selected Records are unique from the Edited Records, but the combined Selected Products with Edits includes all Selected with any Edits made to those records.

47 thoughts on “Let’s Revisit How to use both the Selected and the Edited records in a Datatable

  1. This is great, but missing something. If either collection is empty (i.e. if there are no selected rows that aren’t edited, and/or none of the edited rows are selected), the assignment will add a null value to the combined collection. You need a way to trim null values from the result (but I’m having trouble finding one without resorting to a loop). Assignment > Add to a record collection quite happily adds null records.

    Like

  2. Apologies as I’m not sure where else to ask this, but I am trying to display via datatable an sObject record collection variable and am getting errors using v2, and I’m wondering if this is possible with any version of Datatable?

    I have a Get Records element whose output is displayed in a Datatable. The user is to select however many records they like, then from there I use their output in a subsequent loop to:
    Build an sObject variable for a completely different object
    Add the sObject variable to an sObject collection variable.

    I then try to use that sObject collection variable as an input to a new Datatable and get the error:
    Apex Action error: Unable to read SObject
    undefined

    And the table is empty.

    I can see in the debug log that the tableData input can see the records within the sObject collection variable, but nothing is displayed.

    Is this something that is possible with Datatable? I can update to the latest release but couldn’t find something specific to this in the release notes. Or do I just have a setting wrong?

    Like

  3. If you are creating new records in the flow and want to display them before you commit them to the database with a Create Records, then you will need to include a unique “dummy” recordId with each record.

    The record Ids need to start with the same first 3 characters as the SObject you are displaying.

    ie. Account is 001, Opportunity is 006, etc.

    Liked by 1 person

    1. Hi Eric, for the OpportunityLineItem I created a fake Id: 00k3F000137l7R1QAI. I am changing two digits in the middle to make a unique fake Id. Is there a validation? I could not get it to work. Thanks in advance.

      Like

  4. Thank you so much, for all this, Eric.
    And tmd for asking the question I guess I didn’t know I was looking for. That saved me future headaches.

    Where I’m running into an issue when I combine the two – I have temporary IDs, and running the Compare action … in a multicurrency org. I’m hitting a null pointer/de-reference a null object error in Currency field lines in the controller. This is the comment immediately above the block:

    // Convert currency fields to running user’s currency

    I was able to get around this by commenting out those 17-ish lines, currently 311-328. I have not yet tested what it looks like if leave those lines commented and start playing in other currencies, but thought I’d see what your thoughts are while it’s fresh in my mind.

    Like

      1. Sure thing.

        Our flow is to add Products to an Opportunity. We lookup and display Price Book Entries for selection, then translate those PBEs to OLI with a temporary ID, with the appropriate prefix.
        We present the temporary OLI for price entry in a second datatable component.
        Between the product selection and price entry screens are where we hit the error.

        16:46:50.0 (48412255)|FATAL_ERROR|System.NullPointerException: Attempt to de-reference a null object

        Class.ers_DatatableController.getRowData: line 322, column 1
        Class.ers_DatatableController.getReturnResults: line 137, column 1
        16:46:50.0 (48428087)|FATAL_ERROR|System.NullPointerException: Attempt to de-reference a null object

        16:46:50:024 USER_DEBUG [317]|DEBUG|currencyFieldsQuerySELECT Id, convertCurrency(Sales_Price__c), convertCurrency(TotalPrice) FROM OpportunityLineItem WHERE Id IN :records

        So since it’s trying to query records that don’t really exist?
        I had a previous version where I was inserting OLIs then displaying them in the table, without incident. But we were finding problems with abandoned transactions and products not having correct pricing.

        I also have a second flow that is specifically for editing existing products. This does not present with any errors, but is giving us some strange behavior with how each currency displays, which labels are on each value, depending on the combination of the user’s currency settings and the currency set on the opportunity itself. This is a very limited use case, but the users in this org are set up for USD, but some do have transactions in other currencies.

        Like

  5. Yes, the problem is because it is trying to query records that don’t really exist. I think the only workaround would be to have my code trap this type of error and ignore the currency conversion in a situation like this.

    Like

  6. Hi Eric,

    Thank you for your efforts. I quite could not understand what to put on ‘SourceUniqueId’. Should we just write Id and that’s enough? Or should it be a variable?

    Best,
    Argen

    Like

  7. It should be the API name of the field that will compared with the field whose API name is listed in the TargetUniqueId attribute. In this example “Id” is the field API name used for both.

    Like

  8. Hi Eric,

    I’m having a an issue updating the records in the collection my edits within the datatable are “saved”, in the debug log it isn’t assigning them to my Output Edited Rows (Store Output Values to Select Variables)
    Outputs:
    outputEditedRows = bomEditedRows ([])

    Any ideas on why not?

    Like

  9. Good day Eric and apologies for hijacking this thread. I run into a pickle with the component.

    Flow:
    I collect records via the Apex Action (perform SOQl) from unofficial website.
    Then I feed the collection into the data table.
    Record appear on the screen. I then choose the records via clicking at the checkbox on the header and some weird things happen…

    Flow screen splits into two. Top half contains the table, with all records selected and bottom part contains again the table component with the records not selected. The bottom half looks like, the portion where the Previous – Next buttons reside, was elongated to account for the size of the table component.

    Any advice on why this is happening?

    Like

  10. Forgot to mention here that the following message also appears
    Unfortunately, there was a problem. Please try again. If the problem continues, get in touch with your administrator with the error ID shown here and any other related details. Error ID: 356806609-323848 (-414970662)

    But the debug logs captured nothing….

    Like

  11. Sometimes there can be issues with record collections when the object contains certain specialized fields such as geolocation fields. Is your SOQL only selecting the fields you want to use in the table? Are there any compound fields included (geolocation, name, address, etc?) Also, make sure you have a Key Field specified that has unique values. The default is Id.

    Like

  12. Good day Eric
    The function – Configure Columns – seems to not be working. When I press the button a new pop up screen opens up with a spinner. After giving it some time the spinner is still moving (spins) but the screen behind it shows the “broken”symbol. After pressing escape I am greeted with the following message:

    Save your flow and refresh the page. If the problem persists, contact Salesforce Customer Support. Error ID: -119861941.
    If you’re working on a screen component, check your component configuration for invalid values.
    Uncaught TypeError: Cannot read properties of undefined (reading ‘forEach’) throws at https://llg.lightning.force.com/auraFW/javascript/7FPkrq_-upw5gdD4giTZpg/aura_prod.js:21:16153

    Unfortunately I can not include an image to this post

    Also the components speed when configuring it presents a heavy lag. Has anybody else or you noticed the above OR this is due to my machine?!

    Like

    1. I see the same problem with the same error message – no solution how to successfully configure Datatable component:

      “If you’re working on a screen component, check your component configuration for invalid values.
      Uncaught TypeError: Cannot read properties of undefined (reading ‘forEach’) throws at https://xxx.lightning.force.com/auraFW/javascript/7FPkrq_-upw5gdD4giTZpg/aura_prod.js:21:16153

      I can start the “Datatable Configuration Wizard” flow in debug mode. I tried both with inactive and active version – no success so far.

      Like

    2. If you see this error when trying to Configure Columns, try going to Setup > Security > Session Settings and unchecking “Enable clickjack protection for customer Visualforce pages with headers disabled“. Also, in your Setup > User settings, make sure both Debug Mode and Development Mode are Unchecked.

      Like

  13. Eric, I posted a comment yesterday about an error with Service Appointment. I was able to resolve it by reading some of your previous comments – sorry I didn’t search more thoroughly. By only selecting specific fields in my Get Records, I am able to use Service Appointment successfully. Thanks for creating this component.

    Like

  14. Hi Eric,

    I’m experiencing what I feel like is a bug. It looks like the component is having a hard time keeping track of whether or not a record has been edited. I run my query, set up the collection variable and bring it into the screen. When I try to edit a value for one of the records, I’m seeing a whole bunch of different outcomes with no clear logic for why one thing happens over the other:
    – It instantly “forgets” and reverts back to old value
    – It remembers for a bit, but then reverts back
    – If I click outside of the datatable, it seems to be more consistent in remembering the edit and keeping it.
    – Sometimes, even though the datatable UI doesn’t indicate any edits, the edit is still sent along to the next step of the path when I click “Save”. However, I still need to completely refresh the flow in order for the change to be reflected visually. This is further indicated by the fact that my variable “varNumberOfEditedRows” is sometimes empty and sometimes accurate.
    – This seems to not be an issue with picklist fields. Any changes are remembered in the UI and sent along in the flow. I still need to refresh the flow/page to have actually show the update though – but this might be something I need to fix myself by making sure the flow comes back around the right direction and actually re-runs a functional query.

    It is worth noting – once I’m done reviewing and updating the data, I reconnect it back up top to re-run some queries and load the flow again, so a user should never have to update the page to see what they should see.

    Any idea what’s happening here?

    Like

    1. There is currently a bug like this when your screen also has a Section on it. Please try without any sections on the screen and see if it then works as expected.

      Like

      1. Thanks for the quick reply! You are right, the issue was solved by removing the Section. Of course, this creates a bit of a UI-headache 😀 Any estimates on when that’ll be fixed?

        Thanks!

        Like

  15. Hi Eric, I really appreciate all of the hard work you put into providing you software. I was wondering if there is any plans to have an output variable that contains everything in the table, that is changed records and records not changed. Before presenting the table, I pre-populate some values so the user may choose not to make updates or they may update one record and leave others as is. I need all to take the next step to get their confirmation before updating the records. For my use case, they won’t be selecting records. Any suggestions on how I can handle this?

    Like

    1. You can solve this use case by installing the collections actions (https://unofficialsf.com/list-actions-for-flow/) and using the Find Common and Uncommon records action. Use the Id field as the UniqueKey Field for each collection and pass into the action, your original collection as the Source collection and the Edited Rows collection from the Datatable output as the Target collection. Create your final collection with an Assignment sets a new Record Collection Variable equal to the Source Unique Collection output of the action and then Adds the Target Common Collection output of the action to it.

      Like

      1. Thanks Eric, for your replay, I did use that action followed by the join action. I am getting the hang of using these actions and they are great.

        Like

  16. Hello Eric. I thank you for the effort and care you have poured into this component and the enormous contribution you make to our community.
    I have a question that can either be very simple or very complicated 🙂 Is there a way to validate inputs in the datatable while saving and before clicking next to go to the news scrren in a flow?

    I have two fields “Allocated Quantity” which is initially zero, and “Maximum Quantity” which is a number greater than zero. I could be displaying this for 20-30 rows.
    I would like to validate on input that Allocated is not greater than Maximum. I can always do this when the flow resumes after the screen containing the datatable when user clicks Save + Next, and go back to the datatable element displaying the error message, but this will reset the value the user entered in “Allocated Quantity”. This would not be a problem if there were only one or two record rows, but I could be displaying 20 rows, and user will not be happy if all 20 rows get reset to zero for Allocated and they have to do it all over again.

    Is there a way to accomplish this validation before exiting the screen component containing the datatable?

    Thanks again!

    Like

    1. Before the datatable screen, set a collection variable (vDatatableCollection) to be equal to your collection of records to display in the datatable. After the screen, use the USF Collection Action (Find Common and Uncommon Records – https://unofficialsf.com/compare-contrast-two-record-collections-with-findcommonanduncommonrecords/) to compare the original collection (source) with the Edited Rows output from the datatable (target). Use the key field (Id) to compare the two collections. Then reassign vDatatableCollection to be equal to the SourceUnique output from the compare action, then ADD the edited rows collection to vDatatableCollection. Sort vDatatableCollection if necessary and connect back to the datatable screen. You will now be displaying all of the orginal records except that the edited values will now be part of the collection.

      Like

      1. Thank you so much Eric. I have implemented this suggestion and it is working great!

        Like

  17. Apologies if this came in twice

    Thank you so much for the component. it is truly amazing and has saved my @#$ on numerous occasions. The reason why I am contacting you is because of the following message I receive on a fresh sandbox (latest edition) with flow running on API: 55

    Message: Screen component ‘LocationTable’ contains type mapping ‘T’ that is not an ‘SOBJECT’.
    The object used here is the location object

    On the same flow I am loading on another table component Product2 and have no issue with this one.

    Please advise

    Like

    1. The Location object is a bit of a snowflake that doesn’t play well with other areas of Salesforce. I plan on writing a Blog Post to describe how to make it work. Here is a quick overview that hopefully will get you on the right path.

      How to use the Location Object with Datatable

      Select the “Input data is Apex-Defined” option in the Advanced section. Note: You will need to manually configure the columns in this section as outlined in the Datatable documentation.

      Install the Serialize/Deserialize SObject Flow Action from unofficialsf (https://unofficialsf.com/pass-objects-to-flow-via-rest-with-the-serializedeserialize-collection-actions/)

      Create a subflow to convert a collection of Location records to a Datatable Record String to be used as the Data Source for the datatable.

      Subflow: Location – Convert to Apex-Defined for Datatable

      Define a Record Collection Variable (colLocations) for the Location object as Available for Input
      Define a Text Variable (jsonColLocations) as Available for Output
      Define a Text variable (vAPI) as Available for Output – (Optional) this returns the current API as on optional value for your flow

      Define a Text Formula (fExtractAPI)
      MID( {!Serialize_Locations_to_JSON.serializedSObjectString}, FIND( “/data/v”, {!Serialize_Locations_to_JSON.serializedSObjectString} )+7, 4 )

      Define a Text Formula (fStripHeaders)
      SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( {!Serialize_Locations_to_JSON.serializedSObjectString}, {!fExtractAPI}, ” ), ‘{“type”:”Location”,”url”:”/services/data/v/sobjects/Location/’, ‘”‘ ), ‘”},”‘, ‘”,”‘ )

      First Flow Node – Serialize Locations to JSON
      Apex Action – SerializeSObjectToJSON
      Object for “inputSObject” (Input) – Location
      Object for “inputSObjectList” (Input) – Location
      inputSObject – Don’t Include
      inputSObjectList – Include – {!colLoactions}

      Second Flow Node – Strip Record Headers
      Assignment
      {!jsonColLocations} Equals {!fStripHeaders}
      {!vAPI} Equals {!fExtractAPI} (Optional)

      How to Use: Pass the collection of Location records into the Subflow and use the jsonColLocations output from the Subflow as the input to your datatable.

      Like

      1. Good day Eric. Apologies for the late reply, but this was task has been shelved for a while. Yesterday I managed to return to it, configured the sub flow as per your instructions. Proceeded to include the table, choose – Apex defined, clicked saved (within the screen) – all good so far. Clicked Save within the flow and I received the following error:

        Locations2 (Screen Component) – Screen component ‘Locations2’ contains type mapping ‘T’ that is not an ‘SOBJECT’.

        Do you have any advise on this?
        Flow API: 55
        Table component: 4.0.3

        With regards
        Dimitrios

        Like

  18. Hi Eric, thank you for this component and the great blogs!

    For this particular use case (combining selected and edited), it only works if the edited rows were also selected.

    When the user edits the value on one row and does not select any rows, then the edited row ends up in the targetUniqueRecordCollection. Since we are only including the sourceUnique and targetCommon, the combinedCollection ends up empty.

    I tried just adding another assignment to include targetUnique. This also fails because the assignment is adding an empty collection. It looks like I need to check for empty collections for source and target before adding them to the combinedCollection. But I can’t use “is null” because the empty collection has “[]” instead of null.

    I really wish the datatable would output one row for each row in the datatable, with any edits, and indicate if it had been selected.

    Regards!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s