cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hammer117
New Member

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

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

8 REPLIES 8
joaodsantos
Resolver III
Resolver III

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:

joaodsantos_0-1664533543069.png

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

joaodsantos_1-1664533671300.png

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
Super User
Super User

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.

 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
a33ik
MVP

@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.

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
Super User
Super User

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?

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
joaodsantos
Resolver III
Resolver III

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

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
Resolver III
Resolver III

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)

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,784)