Unable to Patch Status Default Choice Column into Dataverse Table

[sthota]

We have a requirement to import the data from one entity to another within the same environment. We are trying to use ForAll with a Patch statement as follows.

//Inside ForAll -> Patch Statement code
Status: If(
                CollectionCreatedOutOfSourceTable.Status = 'Status (SourceTable)'.Open,
                'Status (TargetTable)'.Open,
                'Status (TargetTable)'.Closed
           )

We do not see any syntax error with the above piece of code. However, we are getting the following runtime error.

statecode: the specified column is generated by server and cannot be specified

We are getting various errors such as expecting an option set value or default schema has been changed when we try to modify the code. FYI, the Status column has the same data type and the same set of choices such as Open/Closed in both the source and target tables

If we remove the Status column from the patch statement then it just seems to work fine.

FYI, we do not have any issues patching the default status column inside the target table in the new app.

Thank you!!

[Z_Lukowski]

Hello,

So I think I know where the problem can be hidden,

Check the dataverse if you don't have 2x Status field because there is a "status" field that is generated by the system and it cannot be changed, and you may have the custom one created in your table, they both can have the same name, 

simple example which I've created on my table:

Hope that helps,

BR

Zbigniew

[sthota]

Hello @Z_Lukowski ,

Thank you so much for your prompt response. As you mentioned, we are using the default Status column i.e. statecode as highlighted above in the screenshot, and changed the options from Active/InActive to Open/Closed. However, we did the same thing in the source table as well as in the destination table and we could get it to work without any issues so far. We did patch around those in our application too. We are having this issue only when we are trying to migrate from one table to another. 

I believe you're right here. I could recollect trying to create a new record into the Dataverse table using an Edit form and patch the Status column into the table as Active/InAcive once at the time of record creation, but we couldn't do that, as it expects always an Active Record into the table. It doesn't even accept the Status column into the Patch statement, even though we would like to patch Active status.

Probably, we can import all-new records as default open records into the target table then update as closed for the closed records (or) a new Status field a custom column into the table as you recommended

Thanks,
Srinivas

[Z_Lukowski · Accepted solution]

Hey,

So Ive made a little test and it seems you cannot add Inactive record, but you can deactivate it after:

When creating record:

When updating existing record:

So basicly you need to create records first and then deactivate them, after Initial Patch you need to find your newly created records (I dont know the condition you have with initial Patch), but it will be something like this:

ForAll(yourCollectionName As TempName, If(TempName.Status( 'Source Table')= 

. Closed, Patch(NameofDestinationTable, ID = TempName.ID, {Status: Status('Target Table').Closed}))

Or you can Create New collection with Filter based on status and if you have GUID, you can go bananas and use Function Patch(NameOfDestinationTable, YourFilteredCollectionName) - 

[sthota]

@Z_Lukowski It helped us attain the solution in two steps as mentioned above.
1. First import all records as Open.

2. Update Closed records in the old table to Closed Status in the new table using below

// Update imported closed records to status Closed into new table
ForAll(
    // Filtering closed records in the old table
    Filter(
        OldTable,
        Status = 'Status (OldTable)'.Closed
    ) As colOldClosedTableName,
    // Checking the condition
    UpdateIf(
        NewTable,
        'Table Unique ID' = colOldClosedTableName.'Table Unique ID',
        {Status: 'Status (NewTable)'.Closed}
    )
);

Thanks !!

[Z_Lukowski]

@sthota  - Thank you, remember also that UpdateIf Function is not delegable, by default it will only change status of 500 records, you can set it in canvas app up to 2000 but thats it, you cannot use it beyond that number, if number of record exceeds 2000, you need to use Patch or set more restrictive filters,

BR 

Zbigniew

[sthota]

Thanks for reminding us @Z_Lukowski about the delegable issue with UpdateIf. This is a one-time activity and the closed records are below the thousand mark, so it should work with the settings change as you mentioned above.