How to prevent creating a child record based a parent field value

[Hammer117]

Hello,

I have a table Invoice with a state field (open, in progress, and closed), the parent table.

I have a child table called Invoice_detail.

I have to be able to add record to the Invoice_detail table only if the Invoice state = in progress

How can I do this?

I want this rule to be enforce also when used the web API.

Regards

[joaodsantos]

Hi,

I think this can be achieved with a Real Time Workflow.

On Create of Invoice Detail, check if the parent status is In Progress, if so cancel the workflow. The Cancel Message will be displayed to the users as a pop-up error.

From the Solution view select New -> Automation -> Process -> Workflow

Give your WF a name, select the invoice detail table and make sure to untick the "Run in background" option:

When the classic editor comes up make sure you have the following settings selected:

Add a condition that checks if the parent status is not "In Progress", then Stop Workflow Task with Cancelled if that's true.

Save WF, Activate and give it a try.

[dpoggemann]

Hi @joaodsantos ,

Wouldn't the record already be created at this point since it is firing "After" the record is created?    

@Hammer117 - I don't know of a way to prevent this from happening especially through the webapi only.  One thought here would be to do the following:

1. Utilize API Management to create your own set of APIs around this (i.e. to add an invoice detail line to the invoice)
2. The API Management API will utilize a service principal / application user that will be setup with the permissions on the invoice lines.  Setup the API Management request to check status of the Invoice and if not in the right status then return error that not possible.
3. Any other users that access the Web API would be provided roles that would restrict the access to creating Invoice lines to keep the control of this through the Azure API Management APIs.

Just my thoughts on what could be done here.  Might be other options, hopefully it his helpful though.

[a33ik]

@dpoggemann from the DB Perspective - the record will be created in the transaction and "Cancelling" of the operation will cause an exception and roll-back of the transaction, so the record won't be added.

[joaodsantos]

Hi @dpoggemann 

Don't think that's the case, it will still prevent creation. Like @a33ik said, because this is a realtime WF it runs as part of the create transaction, and if you cancel the workflow you cancel the whole transaction and it'll roll back. It's a good alternative for these types of small validations and ensures it runs server side so it'll affect anything that goes through the API as well (don't think we can do this sort of cross entity validation with Business Rules).

[dpoggemann]

Hi @a33ik , @joaodsantos 

Thanks!  Learned something.  Never tried this to just cancel on real-time workflow.  Now, is there a way to determine this is coming from the web api only as requested?

[joaodsantos]

Don't think there is, at least not that I'm aware of. I was trying to think if there's any property that could be used on the plugincontext object and just rewrite the above as a plug-in but even with that I'm not aware of any property that has that detail.

But unless I'm wrong, @Hammer117 mentioned "also when used", so I read it as, not just from form but also from API, I don't think they meant exclusively through API, but could be wrong!

Let's see what they say

[Hammer117]

unfortunatly it does not prevent the record of being created. The trigger is record created... So the WF is called atfer the record creation and the Cancelled = true does not undo the situation.

[joaodsantos]

That's strange, I was able to replicate that functionality on a demo environment with contact/account relationship, only create a contact if parent account has value on field X and workflow always kicked in if the field on account was empty.

Any chance you can share a screenshot of your workflow?

Like we mentioned above, the WF runs as you run create the record. Can you also confirm you've select it to run in real time? (not in background)