Importing records as find requests

[adyf]

Just wondered if it was possible to export records from one database and then import them as find requests in another database. I would then have the same set of records showing in both databases as a result of the find.

[bcooney]

Not really understanding this question, especially since "database" is not a FM term. Do you have two FM files? Or, two tables within the same file? Please be more descriptive of the tables and data.

[adyf]

Not really understanding this question, especially since "database" is not a FM term. Do you have two FM files? Or, two tables within the same file? Please be more descriptive of the tables and data.

I deliberately stated databases as they are not tables within the same file. I should have stated that they are two seperate files.

[comment]

You cannot import records as find requests. Technically, there are other ways to reproduce a found set in another identical file - but it would help knowing what this is about.

[adyf]

I didn't think that you could as I tried to do it manually. The two files both have a field with the same data in each. I would like to automate the task of finding the same found set in the second file as it can be quite time consuming performing the find manually depending on the number of records which can vary.

I suppose I could export the field that they have in common from one file and then import as new records in the other file. This would then give me some duplicates as the found set. I could then search for the original records and delete the newly created duplicates, leaving the original records as the current found set.

[bcooney]

That's really not much clearer. Is this something that might be handled by creating a relationship btw the two files?

[adyf]

That's really not much clearer. Is this something that might be handled by creating a relationship btw the two files?

Perhaps, I need to give this some more thought.

[bruceR]

Just wondered if it was possible to export records from one database and then import them as find requests in another database. I would then have the same set of records showing in both databases as a result of the find.

This discussion would probably go a lot better if you would explain much more about what task or purpose you are trying to perorm and what these files represent. Or provide copies of the files.

[adyf]

This discussion would probably go a lot better if you would explain much more about what task or purpose you are trying to perorm and what these files represent. Or provide copies of the files.

I'll try and make this clearer using screen grabs. In the first file below using myself as an example (first 6 records)you can see sections of railway lines (UK) that I have the competence to drive trains over. In this file each Train Driver has a seperate record for each section of line. In the second file (second screen grab) each record is a section of line only. I inherited these files after a colleague retired and he used the second file to compare data to the first file in case of deletion or corruption of data. In the second file each Train Driver has a seperate field of their initials. If a Train Driver has competence over a section of route then his initials are placed in his individual field. In this case you can see the same six records as the first file with my initials (AF) in the 'AF' field. I have some new Train Drivers with records in the first file and I would like to add their intials to the corresponding records in the second file. Is there a way to find that found set in the second file so that I can add initials easily to the found set by replacing field contents? Up until now this has always been a tiresome job using manual finds. Some Train Drivers have in excess of 300 records (route sections). You may well notice that the route code from the first file matches the section code of the second file, each one is unique. Can anything be done via a relationship between route code = section code?

[bruceR]

I'll try and make this clearer using screen grabs. In the first file below using myself as an example (first 6 records)you can see sections of railway lines (UK) that I have the competence to drive trains over. In this file each Train Driver has a seperate record for each section of line. In the second file (second screen grab) each record is a section of line only. I inherited these files after a colleague retired and he used the second file to compare data to the first file in case of deletion or corruption of data. In the second file each Train Driver has a seperate field of their initials. If a Train Driver has competence over a section of route then his initials are placed in his individual field. In this case you can see the same six records as the first file with my initials (AF) in the 'AF' field. I have some new Train Drivers with records in the first file and I would like to add their intials to the corresponding records in the second file. Is there a way to find that found set in the second file so that I can add initials easily to the found set by replacing field contents? Up until now this has always been a tiresome job using manual finds. Some Train Drivers have in excess of 300 records (route sections). You may well notice that the route code from the first file matches the section code of the second file, each one is unique. Can anything be done via a relationship between route code = section code?

Sounds like a data structure problem. The AF field should not exist. There should be a join table with fields SectionID and DriverID.

[bcooney]

I agree with Bruce. You have a standard many-to-many requirement (many Sections, each with many qualified Drivers). This is resolved with a join table btw the Driver table and the Section table. Each Driver, as Bruce points out, should have a unique ID (the key in the Driver table along with NameLast, NameFirst, Initials, etc.). With this proper structure, you'd easily be able to see the Sections for which a Driver is qualified (place a portal based on the join table on the Driver form using the relationship from Driver to Join by DriverID).

Assignments of Drivers to Sections would require a different table.

[comment]

I inherited these files after a colleague retired and he used the second file to compare data to the first file in case of deletion or corruption of data.

Thanks for explaining that. I thought perhaps the two files were in separate locations, and you wanted to "send" a found set from one to the other. If the files are part of the same solution, you can easily create relationships between any two tables, regardless of which file they are in. In fact, you could simply combine the files by importing the tables of one file into new tables in the other file.

Also make sure you do periodical backups - this provides a much better protection "in case of deletion or corruption of data".

In this file each Train Driver has a seperate record for each section of line.

That's good - so you already have the join table (or at least a good starting point for one). If I understand correctly, you also have a table where each route/section is a record - so you only need to add a table where each driver has its own unique record - and switch to using serial IDs instead of names/initials.

[adyf]

Thanks for explaining that. I thought perhaps the two files were in separate locations, and you wanted to "send" a found set from one to the other. If the files are part of the same solution, you can easily create relationships between any two tables, regardless of which file they are in. In fact, you could simply combine the files by importing the tables of one file into new tables in the other file.

Also make sure you do periodical backups - this provides a much better protection "in case of deletion or corruption of data".

That's good - so you already have the join table (or at least a good starting point for one). If I understand correctly, you also have a table where each route/section is a record - so you only need to add a table where each driver has its own unique record - and switch to using serial IDs instead of names/initials.

Thanks to all for your replies. I understand that a join table would be better structurally, but not sure how it helps with my original problem. The suggested join table sounds very similar to file 1 and would have the exact same number of records. One record per Train Driver per each section of line.

[comment]

I understand that a join table would be better structurally, but not sure how it helps with my original problem.

It helps not by solving the original problem, but by eliminating it. If a driver is qualified to drive over a section, then there will be a record in the join table, joining that driver to that section.

Alternatively you could set a Qualified field in the join table to True when that driver is qualified to drive over that section. Although I don't see why you would need the other records, joining drivers to sections they are NOT qualified for.

This moves the problem entirely into the realm of data and does away with the need to add a field for each new driver.

[adyf]

It helps not by solving the original problem, but by eliminating it. If a driver is qualified to drive over a section, then there will be a record in the join table, joining that driver to that section.

Alternatively you could set a Qualified field in the join table to True when that driver is qualified to drive over that section. Although I don't see why you would need the other records, joining drivers to sections they are NOT qualified for.

This moves the problem entirely into the realm of data and does away with the need to add a field for each new driver.

Presumably the two required fields in the join table, DriverID and SectionID must be fields in their own right and not calculation fields from the other two files?

Can the join table (or can it be a seperate join file?) be set up to manage itself? For example, when new sections are assigned to a Driver they are automatically added to the join table. However, if I remove sections from a Driver I would like to remove them manually from the join table just in case records are removed inadvertently from the Driver file.

I am intrigued by this solution as it would help to further my FM education but going back to the initials fields, I did manage to create a way of comparing Driver file with section file for an individual to make sure identical sections were showing in each file. Surprising how many errors my colleague made entering data manually in both tables. I presume if the join table can create records automatically then that will keep things in sync apart from if data is entered incorrectly in the Driver file.

[comment]

Can the join table (or can it be a seperate join file?) be set up to manage itself? For example, when new sections are assigned to a Driver they are automatically added to the join table.

I'm afraid you are missing the point: new sections are assigned to a driver by - and only by - adding records to the join table. This information is not recorded anywhere else.

I'd suggest you look at the demo file here:

http://www.fmforums.com/forum/showpost.php?post/246136/