integrating records from two sources in one master database

Discuss the HanDBase for Windows Desktop program, conduits, and add-ons.

integrating records from two sources in one master database

Postby Avi » Mon Jan 03, 2011 10:25 am

Howdy all,

Hope y'all are having a wonderful holiday season, whatever weather you may be experiencing ....

For years I've been a one-person HanDBase user, but now I need to add records to my databases emailed to me by my field manager. I'm looking for suggestions on the best way to do this, keeping in mind a couple of caveats:

1. My databases are pretty connected to each other with many Link/Linked connections, which in combination with custom forms has served me well in allowing efficient data entry while out in the field. However, I seem to recall reading here on the forums that these Link/Linked connections depend on a unique internal record number to function properly, and once more than one source is contributing records to a given database, that internal number may in fact not be so unique after all, and things may get bolluxed up. Am I recalling incorrectly, or if not, what should I do about the situation?

2. In order to make it easier to keep things straight, I'd like to add a field to each database that always contains the name of the person who contributed that record, so in each row (record) of the master data base I can see who added the data. I tried creating a text field with a default value of the person's name, but it doesn't get filled automatically. How can I achieve this so it's transparent to the user?

3. I'd like to somehow make it easy not to inadvertently add the same record twice, if it was mistakenly emailed to me on two separate occasions. I've thought of simply adding a "checkbox" field called "sent", but that requires manual intervention and isn't so elegant. Any other ideas?

4. I see, on the menus, a couple different ways of actually adding the records emailed to me. One way is to open the received database and then do "Actions/Copy Records To". Another appears to be to open my master database and then do "File/Merge Database" with the emailed one; however when I tried this and then as an experiment deleted one of the "imported" records from the master database and then tried to "merge" it again just to see what would happen, it did NOT bring the record in the second time. How do each of these methods work, and which way should I be doing this?

thanks in advance for any and all advice.
-avi
Avi
 
Posts: 72
Joined: Thu Jun 04, 2009 1:36 pm

Re: integrating records from two sources in one master datab

Postby dhaupert » Tue Jan 04, 2011 1:12 pm

Hi Avi,

Great topic! HanDBase has built in support for handling more than one source- we store internally a record identifier and this prevents things like duplicate records. If you're using the Link and Linked, it too uses values that keep their relationship unique despite multiple users creating records. So in this case, the File|Merge Database function is a great way to merge databases from different sources. When you do this, the resulting database is the same as if you did a synchronize between the two databases. This works in most cases, the only exception being a caveat of sorts- if a database record is deleted from a database, it's marked as such internally and on the merge, that record will be deleted in the final merged database. So if you had two people adding records, and one decides to delete to make room for other records, those records will then be deleted from the master on the sync. It seems more and more that this is a problem in situations with multi-users and I plan to address this in future versions with an option for ignoring deletes. But in the meantime, I'd suggest you don't delete records from databases unless you actually want them gone from the master database as well.

Hope this helps!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: integrating records from two sources in one master datab

Postby Avi » Wed Jan 05, 2011 11:49 am

Dave, thanks for the informative reply.

The issue you mention about deletion of records being propagated during a "merge" could be answered by the solution you propose, or by another feature I've wanted for years: the ability to lock a record.

Locking records can come in handy for lot's of other purposes besides preventing their deletion, too. Imagine, for example, a database that is essentially a virtual invoice ledger - each invoice (record) must have a unique, unalterable serial number and an unalterable creation date. All the methods for generating a field that automatically increments for each new record have their results affected by the application of a filter to the database, hence altering those fields' contents, something unwanted in this case. If I could lock a record once it is generated, I could prevent that from happening.

In terms of preventing loss of records due to one user deleting them, as things now stand, the merging action may be ruled out for me exactly because of the behavior you described, so I may have to stay with the "actions/copy records to" technique for now.

With regards to my second question about labeling the source of a given record, I found that giving a text field a default value of the specific user's name was not adequate to get all the fields to fill automatically when new records were created. However, if I clicked on the field header in list view and selected "Set values to.." and then set it as I wanted, subsequently created records [i]were[i] automatically filled as I wanted. This isn't something I would have expected to happen, BTW. I would have thought the action would be limited to existing records.

Do you have any thoughts on how I can automate keeping track of which records have already been received and integrated into the database, so as to prevent inadvertent duplication, without using the "merge" function, as I asked in question 3 above?

thanks for your help!

-avi
Avi
 
Posts: 72
Joined: Thu Jun 04, 2009 1:36 pm

Re: integrating records from two sources in one master datab

Postby sljensen » Thu Jan 06, 2011 10:33 am

When using the "actions/copy records to" technique rather than syncing or merging databases do the link/ linked associations remian?
sljensen
 
Posts: 3
Joined: Mon Dec 20, 2010 5:56 pm

Re: integrating records from two sources in one master datab

Postby dhaupert » Sat Jan 08, 2011 12:49 am

Avi wrote:Dave, thanks for the informative reply.

The issue you mention about deletion of records being propagated during a "merge" could be answered by the solution you propose, or by another feature I've wanted for years: the ability to lock a record.

Locking records can come in handy for lot's of other purposes besides preventing their deletion, too. Imagine, for example, a database that is essentially a virtual invoice ledger - each invoice (record) must have a unique, unalterable serial number and an unalterable creation date. All the methods for generating a field that automatically increments for each new record have their results affected by the application of a filter to the database, hence altering those fields' contents, something unwanted in this case. If I could lock a record once it is generated, I could prevent that from happening.

In terms of preventing loss of records due to one user deleting them, as things now stand, the merging action may be ruled out for me exactly because of the behavior you described, so I may have to stay with the "actions/copy records to" technique for now.

With regards to my second question about labeling the source of a given record, I found that giving a text field a default value of the specific user's name was not adequate to get all the fields to fill automatically when new records were created. However, if I clicked on the field header in list view and selected "Set values to.." and then set it as I wanted, subsequently created records [i]were[i] automatically filled as I wanted. This isn't something I would have expected to happen, BTW. I would have thought the action would be limited to existing records.

Do you have any thoughts on how I can automate keeping track of which records have already been received and integrated into the database, so as to prevent inadvertent duplication, without using the "merge" function, as I asked in question 3 above?

thanks for your help!

-avi


Hi Avi,

The default value should definitely work for any new records, but not for existing records. If you're finding this not to be the case, then there seems to be a problem. Perhaps you can email me (address below) with the database in question so I can see if there is a bug or a setup issue?

As far as other ways to merge, there is no way to do it within the HanDBase Desktop outside of the way mentioned above. If you had Access, your best bet is to have some primary fields in the database and using a query to kill dupes where primary keys match. This is a lot of work to set up and run- I have a database I keep of all registered users mailing list email addresses and it's too big to fit in HanDBase (ie, more than 65000 records). For many years, the process was using Access to eliminate duplicates with some queries, but then finally last month I wrote a small application to do this for this specific database file so that I didn't have all the work in Access- very tedious otherwise!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am


Return to HanDBase for Windows

Who is online

Users browsing this forum: No registered users and 1 guest