Originally posted by: PokerChris

I'm working on a scenario where there are parent/child relationships and the input data is similar to this:

Parent/Child Indicator,Fname,Lname,Age

P-Bob,Smith,32
C-Sally,Smith,4
C-Tommy,Smith,6
P-Chris,Gomez,65
C-Anna,Gomez,32

I want to put all the parents in their own db table and the children in a separate table.

My thought as to how to accomplish this would be to create a functional map for the parent rows, capture it's data (along with db auto-generated primary key) and insert into the parent table.  Then within that functional map, I would create another functional map for each child row and pass the PK from the parent table to establish a relationship between the two tables. 

Is that the correct approach for a relational database?  If so, I can't seem to get it working correctly because I can't seem to change the output cards within the nested functional maps to point to the right db table and insert correctly.  Anyone with any thoughts on this situation would be greatly appreciated.  Thank you.

Originally posted by: TonyCrrd

Hi,

You have to create a type tree for the input data that will contain:

- Definition of a child,

- Definition of a parent,

- Definition of a group family (Parent + Childs)

- Definition of a file, which a group of families

Based on this, you can now create a functional map (let's say F_EachFamily) that will loop on each family. 

Into this functional map you will have another functional map (F_EachChild) that will loop on each Child of the family. You can here easily make the link between the Parent and the Childs.

Regards,

Tn

Originally posted by: PokerChris

Is it possible to have nested functional maps where both of the outputs are db tables?  I seem to be having trouble making that work. 

Originally posted by: vijji_WTX

yes, you can have nested functional maps for both of the db tables output. only challenge here is...if any issues comes , how you bring back the error details to main card. Suggestions from TonyCrrD is good to implement.

Originally posted by: Semoul

Is think what you mean is to set a "TargetRule" on your functional maps. That does not work as you can only set those on cards of "main" maps.
You can use PUT or RUN functions though in the rules of your functional maps to access your db.

Also, you would not necessarily need to have nested functional maps. You could have an output card that inserts the parents and another output card that inserts the children using as input your original file and a DBQUERY/DBLOOKUP that extracts the PK information of parent.

Originally posted by: PokerChris

We have condisered the idea of doing the LOOKUP but would prefer to capture the PK for the parent table and immediately pass it to the child table while we still have access to it instead of having to abondon it and then retrieve it at a later time.  If it comes down to it, this will likely be my route though (using the LOOKUP functions.)  Thanks all for the responses, i'll play around with a few of the other functions and see if I can make some additional progress.