Jump to content

Copy data from another field in same table


Recommended Posts

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!

 

Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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?

Link to post
Share on other sites
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 by comment
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.