Recently Written- Bento 3.0.3 Update Is A Hit! February 3, 2010
- Bento As A Motivational tool January 24, 2010
- An Update About The Bento Database Manager / Digestive January 17, 2010
- PDF Archive Option! January 15, 2010
- How To – Template Sharing Part One January 14, 2010
- Template Exchange Problems! January 11, 2010
- Bento 3.0.2: First Crash December 21, 2009
- Strange Bug. Anyone? December 19, 2009
- Free Bento 3.02 Update Released December 10, 2009
- Saving Space On Forms November 17, 2009
- User Finds Bento 3 Easter Egg November 17, 2009
- Bento 3 Crash Report: November 09 November 17, 2009
- Lame Bento 3 Free Holiday Pack November 17, 2009
- Bento Bug: Movie Aspect Ratio October 15, 2009
- Bento 3 Crash Report: 10-12-09 October 15, 2009
Apr
11
Importing Related Data Into Bento
Filed Under Bento Tips
When you link two Libraries via a Related Records List Bento manages the links internally which makes life easier for most people but can present a problem if you want to import two CSV files which contain related data and link them back together. There is currently no automatic way to re-link the data but you can make life much easier with a bit of planning.
Assume that you are migrating some data from Microsoft Access to Bento and the data relates to patients and the dates they attended your surgery. You may well have had a numeric field in your Patients table in Access called PatientID which links to a PatientID field in the Appointments table. This PatientID field should exist in both of your source CSV files which are going to be imported into Bento.
Import the two files into Bento which will result in two Libraries, in this example called Patients and Appointments. Ensure that during the import process you set the field types of the fields being imported properly:
Now that the data is in Bento the two Libraries need to be linked via Related Records Lists. Because there are fewer patient records than there are appointment records it will be quicker to work through the patient records and relate the appointments to them rather than working through each appointments and relating a single patient to each.
In addition, since when you add existing data to a Related Records List you can search for the records you want to add we need a way to quickly find the appointment records for each patient. At first it may seem that the PatientID field would be ideal for this but since it is just a number, searching for ‘1′ would also return records where the appointment date contains a 1. Therefore the PatientID field needs to be made unique and this can be achieved via a Calculation field:
The Calculation field above is simply appending the PatientID field to some text, ‘PID’, resulting in values such as ‘PID1′, ‘PID2′, etc. These values will be unique to this field so the Appointments Library can now be searched with records being returned for relevant patient only.
In a form in the Patients Library add the PatientID and the Patient fields. Also add a Related Records List based on the Appointments Library:
The Appointments Related Records List needs to have the PID field and the Appointment Date field displayed:
Now it is possible to work through the patient records and choose the relevant appointment records for each:
Once this process has been completed the PatientID fields can be deleted from both the Patients and Appointments Libraries and the PID field can be deleted from the Appointments Library:
Any new appointments which are logged against a patient will automatically be linked by Bento.
Comments
7 Responses to “Importing Related Data Into Bento”
Leave a Reply
Simon,
Congratulations on two beautifully submitted tutorials this date, April 11, 2008.
I am so glad with your assistance I was able to set up an RSS feed from my iMac to your website and get notified the date they are put up on your site.
I continue to learn many things from people such as yourself who are unselfish in the giving of themselves so that others might learn. That is a great gift you have.
Thank you so miuch.
David
Alternatively:-
Instead of adding “PID” prefix, sorting, and the deleting PID afterward,
For each patient record:
Sort the Patient ID column in the “Add related records” Dialog. Scroll down to the matching Patient ID and shift click to select all the same ID’s. Then click “Add to list”
Hi Jon and thanks for the tip. This will work really well too but may not be as suitable if there are hundreds or thousands of records to match where a search may be faster.
Hi Simon,
On the contrary, I think this will work faster with a larger number of records than other techniques because Bento™ developers thought of including a scroll bar on the “Add related records” dialog and their sorting algorithm is blazingly quick.
Hi Jon,
Well I guess that this is one of those situations where it is better to let individuals choose their own preference based on which works better for them but, once again, thanks for the tip.
My assistant has an old database (FileMaker Pro 3) that she must access regularly. I too need to access it (link to it), and would like to do so via Bento. That way, each of us has our preferred UI. Is there any way to do this? Any help someone could give would be much appreciated.
John,
the way you could access Filemaker through Bento would have to be through file import/export. You have to export Filemaker files as text files (called .csv files). Those files can be imported into Bento. And vice versa.