Import Data into a child table and use same UUID as the parent

[Mike_FM]

Hi all,

I am creating a database with a simple set up; one table called shots (parent) related to another table called scans (child).

I have created a portal in the shots (parent) table with a scripted button called 'create new scan record' that when pressed, creates a new record in the scans table attached to the same UUID from the shots table. This works well and links records from both tables.

The problem I have is, I need to import a lot of data via a TabD into the scans table - probably a hundred records at a time that need to share the same UUIDs as the table records so they are linked and I'm not sure how to do it.

Is there a script I can use to achieve this? Can I import data from the TabD straight into the Scans portal which is in the Shots table?

I'm a FM newbie so any insight will be greatly appreciated

Thanks

Michael

[comment]

To clarify:

All the scans from a single import are to be linked to the same shot - and this shot is a new shot that needs to be created?

And what exactly is "TabD"?

 

[Mike_FM]

Hi there,

There are multiple shots that have already been created and the single scan import will contain multiple records some of which will need to relate to just one shot

TabD is tab delimited file

Thanks so much for you help!

[comment]

16 minutes ago, Mike_FM said:

the single scan import will contain multiple records some of which will need to relate to just one shot

This is just getting more confusing: what does "some of which" mean? If only "some" need to be related to a shot, what about the others? And how can they be told apart?

 

[Mike_FM]

the single scan import will contain multiple records some of which will need to relate to just one shot

The scan import will create multiple records in the scans table; each scan record needs to relate to a corresponding shot record in the shots table. There will also be multiple scan records that will relate to just one shot record

ie 1 scan record links to shot 0010 but 5 scan records that link to shot 20

The shots and scans are told apart by their names

eg

shots are labelled '0010' 0020' '0030' etc

scans are labelled '0010_scan01' '0020_scan01' '0030_scan01' this is how I know what scans go with what shots - every shot will always have one scan

but some shots have multiple scans eg shot 0040 has three scans and they are named '0040_scan01' '0040_scan02' '0040_scan03'

The names of the shots and scans go in name fields in the records

 

 

 

 

[comment]

So if I am following this correctly (which is not at all certain), you could have a calculation field in the Scans table =

Left ( Name ; 4 )

and use this as the match field opposite the Name field in the Shots table.

 

[Mike_FM]

Hi thanks for this, sorry if my query has come across as confusing, the bare bones of what I am trying to achieve is this:

I have a parent table containing ID and some other basic fields. There is a child table feeding into this - with its own set of fields. 

Parent: __pkParentID

Child: __pkChildID, _flParentID,...(other fields)

I have many records that I wish to import from an excel doc and a tab delimited file into the child table. I can do this, but can't work out how to auto populate the parent table.

I can see what you have written above but I'm not sure how to implement it... do I replace 'Name' in your calculation with the name of the field I want to match to?

[comment]

10 minutes ago, Mike_FM said:

can't work out how to auto populate the parent table.

I guess I am still missing something. You said that each imported child record has a field that identifies its parent record - e.g.  "0040_scan02" identifies the parent as "0040". Now, do you already have a parent record that has a field containing the value of "0040"? If yes, then there is nothing to populate. All you need to do is extract the "0040" part in the child record into a separate field and use this to link to the parent. I don't see why you would need or want to use UUIDs here. 

 

[Mike_FM]

Yes there is a parent record that identifies 0040, so I see what you're saying... the separate field that contains the extracted '0040' should be the foreign key and linked to the parent in the relationship graph...

[Mike_FM]

This works perfectly thank you.

I guess my only other question is that I have been doing a lot of research online and there is a lot of back and forth about why using UUIDs is a better way to link tables than using an ID like this one... so as I'm new to this I am wondering what you think about this and if setting up my database this way cold have any pitfalls along the way.

The only potential issue I could foresee is if at some point in the production I am told to change the names of the shot codes (parent) and then nothing from the child records will link 

thanks

[comment]

Any value that is both (a) unique and (b) permanent is suitable to serve as a primary key. We haven't been told how exactly these shot codes are generated, so ... In any case, you don't seem to have any other way to link the imported scans to their parent shots - at least not initially.

If the codes ever change, you could simply put the new codes in a new field and keep the existing relationship with its existing match fields. There is also a way to replace the values in both parent and child records - but I doubt you will ever need this. Note also that you could use the suggested relationship only to copy the parent's UUID into a field in the child's table, and then use these values for your "real" relationship - but I doubt you need the added complexity either. 

 

[Mike_FM]

Thank you