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.

Patients CSV Data.png
Appointments CSV Data.png

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:


Setting Field Types During Import

Click for a Larger View

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:


Creating A Totally Unique Patient ID

Click for a Larger View

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:

Initial Patients Form.png

The Appointments Related Records List needs to have the PID field and the Appointment Date field displayed:


The Appointments Related Records List

Click for a Larger View

Now it is possible to work through the patient records and choose the relevant appointment records for each:


Selecting Patient Appointments

Click for a Larger View

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:

Final Form.png

Any new appointments which are logged against a patient will automatically be linked by Bento.

Comments

12 Responses to “Importing Related Data Into Bento”

  1. rockinchair1 on April 11th, 2008 7:55 pm

    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

  2. Jon Longworth on April 11th, 2008 10:03 pm

    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”

  3. Simon on April 12th, 2008 1:55 pm

    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.

  4. Jon Longworth on April 13th, 2008 1:13 am

    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.

  5. Simon on April 13th, 2008 7:52 am

    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.

  6. John Woods on October 12th, 2009 2:54 pm

    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.

  7. Florian on October 14th, 2009 5:41 am

    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.

  8. Frank Petrallo on April 12th, 2011 6:33 pm

    Love this Doc, Great info!
    Do I Understand the Only way to link two “Imported” Tables (I do come from an MS Access DB) is to create a related Data field then actually go through All 800 Records to Associate the 100 to 500 Records per Record? This sound like more work then Learning FileMaker, Will File Maker relate tables Easier? Thanx

  9. Florian on April 12th, 2011 7:28 pm

    Frank,
    actually, there is one shortcut. If you create a loop of Related Data fields, you at least only have to do half of the work of linking records. Example:
    One Library consists of personal patient data (name, address,…). The second Library consists of service records (doctor visits, prescriptions,…). Then you create two Related Data fields: first you drag the data Library onto the Service Library, then you drag the Service Library onto the data Library to close the loop. If you now link a patient to a visit, that visit is automatically added to the patient Library (into the Related Data field), hence half the work. (I hope I made that clear enough…)
    Otherwise there is no automatic linking, not even if the Related Data field comes from a Smart Collection. I hope that feature will be added someday.

    I can’t speak to Filemaker Pro, I have never used it. You could ask that question in the Filemaker Pro forums.

  10. Julia on June 22nd, 2011 3:22 pm

    I’m also trying to import related data into Bento, and I don’t understand why it’s so complicated! I’m more used to use Access that makes more sense to me. Bento simply looks easier but hides the way it works! Don’t understand how to relate data except linking you’re things one by one… -_-’ This is ridiculous! What’s the point of having this program if it can’t even do this!?

    When I try doing like in the example, when I search the PID#, it gives me all the PIDs that start with the number I’m looking for and not the unique solution (ex: search PID5, it gives me PID5, PID51, PID52, etc).

    Please tell me there is a solution to this and I don’t need to relate manually every of my 800 lines! -_-’

  11. Florian on June 25th, 2011 4:50 pm

    Julia,
    I forwarded your comment to Simon who wrote this post. He must be busy at the moment since he hasn’t responded.
    I can’t be of much help. Bento is NOT a relational database app. What it offers is pseudo-relational data, so I understand your frustration.
    And yes, when you search for something Bento will return results which contain your search term. I don’t know if there is a more exact way to search.
    But you can add any number of records to a Related Data field at once. You can select the whole list with cmd-A for example. Or you can copy and paste whole lists from another app.

  12. Simon on June 27th, 2011 2:46 am

    Hi Julia and apologies for the delay in replying. Unfortunately, as Florian said and as you have discovered, Bento seems unable to perform some seemingly basic functionality and importing and ‘re-stitching’ related data is unfortunately one of those things. There isn’t a simple solution, especially considering the search issue so I’m afraid you either stuck with manually re-mapping your data or switching to something more capable like FileMaker Pro.

    Please, however, do go and post your thoughts in the official Bento forums at http://forums.filemaker.com/groups/a13a2dc617/summary?lang=en_US so that FileMaker are aware of them and one day they may add some functionality to fix this.

Leave a Reply