Copy data from another field in same table

[Chrism]

Hi,

We are importing data from CSV into a list of Certifications into a single table

The data consists of Company name, Certification Type, Company Sector, Company Size, Town and some other fields.

The data we get from another system is quite messy and requires a lot of work to make it presentable, but consistant enough for me to create a Clean up script to Set the fields as wanted. This all works great.

The issue I have is when the Certification type is 'Type2" the data we get from the other system does not have the Company Sector, Company size or Town. So currently someone is manually copying these fields from the matching "Type1". (you can't do 'Type2" unless you have completed "type1")

Is there any way to add onto the end of my clean up script to update the missing fields from data in the the "Type1" record of the same company.

Thanks!

 

[comment]

I don't think we have enough information to answer this. You say:

3 minutes ago, Chrism said:

currently someone is manually copying these fields from the matching "Type1".

If the Type2 record does not have any company details, how does that someone know which record is the "matching" Type1 record?

I suggest you post a small (but representative) sample of the CSV source and explain how you want to fit it into your solution's structure.

 

[Chrism]

the Type2 record does have the matching Company name, just not the Sector, Size or town

[comment]

Why do you find it necessary to duplicate this information across all Certification records? I would have expected a table of Companies, where each company is a unique record, and a related table of Certifications. All the data that describes the company would be stored in the company's record in the Companies table - and only there.

 

[Chrism]

Because each record is a separate certification and we don't have much dealing with the companies themselves and a single line info is all we need for reporting purposes.

I have tried to create a Relationship to new table, and added a 'Set Field 'step in the script in the hope if would create a matching record in the other table and maybe I could do it that way, but it is not creating any records in the other table

Actually got the relationship working for creating records in the other table, I am assuming I can sort this with a relationship?

[comment]

1 hour ago, Chrism said:

Because each record is a separate certification

Yes, each record is a separate certification - but not a separate company. 

Look, it's easy to construct a self-join relationship that would match the Type1 record of the same company and copy the data from there. Or just perform a find to locate that record. I just don't think it's a good solution.

 

Edited November 19, 2020 by comment