I have a entities which has childs, like an invoice with invoice lines.
I am importing this entity (along with its children) through an automated data flow (from json).
The entity can also be updated through the import process (for eg, adding a new invoice line, or changing fields in the header).
Works fine, but I can't seem to find a way to handle deletion of related entities (invoice lines) on already imported entities when on subsequent imports a related entities was deleted (eg remove an invoice line).
I am not sure that I understand the problem but in order to delete a Parent Item, you have to first delete all of the Child items associated with it. Otherwise, you will have what is called "orphan" items, ie items in a Child table that do not have a Parent associated with them.
Makes sense to delete all childs before deleting the parent, as in any referential system, but my question is not that.
How do you delete a record when you do imports through dataflows?
Have you tried using the Delete rows that no longer exist in the query output feature? If you turned this on for both of your Invoice and Invoice Line imports then it would remove any Invoice or Invoice Lines that aren't in your import file. Be careful with this and test it thoroughly as it won't work for incremental imports - it deletes everything that is not in the import source.
Another alternative could be to created a Delete (two option) field on your entities in CDS and then flag the record for deletion as part of the import file. Then use Power Automate to trigger on the Delete field being set and get it to do the actual deletion.
By the way, how are you getting Dataflow to import the two entities and get them to relate to one another in a Parent / Child relationship? I can't seem to figure this one out.
Well, using a field does not seem to work since you can't read previous records from CDS while executing a data flow, which you need to do, to know which records to mark as soft delete.
Importing parent-child entities is almost trivial, though non transactional.
You create multiple flows, 1 for each entity: parent and child.
On the child flow you make sure you generate the key of the parent as well.
No being able to solve this scenario is super bummer; you can't use an import system that has no way to mark specific items as deleted.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.
We are excited for the next Super User season.
FIll out a quick form to claim your community user group member badge today!
Features releasing from October 2020 through March 2021