Populate a FK field in a table from another matching desc

[Haseeb]

Hi

I have two tables

Division{ID,Name}

Branch{ID, Name, fkId_Division, Division Name}

Branch::ID, Branch::Name and Branch::Division Name fields are populated.

My goal is matching Branch::Division Name with Division::Name, grab the Division:ID and put in Branch::fkId_Division field.

Thanks in advance.

[jamesducker]

You should be able to set Branch::fkId_Division as an auto-enter calculation.

Define a relationship between Branch::Division Name with Division::Name, then make Division:ID a calculation field (or auto-enter calculation) equalling simply

division::ID

Hope that helps.

James

[Søren Dyhr]

Doesn't "Branch" and "Division" have a fair amount of attributes in common, enough to keep them in the same table? What makes you believe it is very different entities, and what in particular doesn't rub off?

Is this structure arbitrarily chosen or is it really based on normalization ... to me is it a recursive structure!

http://jonathanstark.com/recursive_data_structures.php

--sd

[jamesducker]

Fair point; I'd also question why you are wanting to join records by NAME rather than by ID.

I think we need to know a bit more about the context, and perhaps a sample file would be handy.

James

[Haseeb]

I am doing this because I am importing data from a Lotus Notes application. So for normalization purpose, I wanted to grab the ID from Division based on a text field. But this is only one time solution and a way to avoid manually typing of all fkIDs. After I am done with it, users will only pick fkId from popup menu(Division table) and will see the Division name and populate only the Division fields in Branch table.

Thanks

Haseeb

[comment]

My goal is matching Branch::Division Name with Division::Name, grab the Division:ID and put in Branch::fkId_Division field.

You need to do exactly what you described:

1. Switch the relationship to match on the name;

2. Replace the contents of fkId_Division field with a calculated result = Division::ID;

3. Switch the relationship back to Division::ID = Branch::fkId_Division.

[Haseeb]

Perfect. All good.

Actually I am from SQL background so really struggling to divert thoughts from that angle.

Please suggest any resources using which I can be successful developer.

[comment]

Please suggest any resources using which I can be successful developer.

LOL, I am still looking for those... :

[Søren Dyhr]

Similar am I struggling with being "successful", but I came to think that a developer with your skills set, might find some of these usefull:

http://www.myfmbutler.com/index.lasso?p=425

http://www.youseful.com/fmplugins/fmSQL

http://www.cnsplug-ins.com/products.htm?product=MMQuery

--sd