Answered

Unable to Patch Status Default Choice Column into Dataverse Table

(0) Share

Report

Posted on by sthota

139

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!!

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Z_Lukowski 216 on at

Like (1)

Report

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

Was this reply helpful? Yes No
sthota 139 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Z_Lukowski 216 on at

Like (1)

Report

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) -

Was this reply helpful? Yes No
sthota 139 on at

Like (0)

Report
@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 !!

Was this reply helpful? Yes No
Z_Lukowski 216 on at

Like (1)

Report

@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

Was this reply helpful? Yes No
sthota 139 on at

Like (0)

Report

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.

Was this reply helpful? Yes No