Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Unanswered: Importing data from excel into Relational field of a Table

    Hi!!
    I am new to BD and still trying to digest bits and pieces of it.

    I have two tables

    Table Name/ Field Name
    1. Employee Table -

    - a) Emp ID - Number
    - b) Emp Name - Text

    2. Employee Punch

    - a) Emp ID - Number -- Related field
    - b) Date - Date
    - c) Time - Time

    I want to import punches from excel consisting of Emp ID, Date and Time Columns in to "Employee Punch" Table, I am able to import Date and Time but not Employee ID Column


    Need Help

    Thanks in Advance
    Last edited by ajayduttn; 07-28-13 at 06:41.

  2. #2
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi
    This not a direct answer but keep in mind BD uses internal record numbers to maintain relationships unlike some databases which can use id's directly (keys). When importing external related data I would have a id field (hidden) on each form for these external imports (as well as a unique BD auto ID numberfor each record). With this data you then can run looping queries/scripts to update matching related records in each form.
    Your excel id column should import in to a text id field on your punch form.
    Regards
    David

  3. #3
    Join Date
    Jul 2013
    Posts
    2
    Sir,

    Can you be more elaborate on the looping and scripting Please...

    That would of great help

    Sorry for my ignorance..

  4. #4
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi ajayduttn
    If you were using many other databases, ie Filemaker, Access etc with a "join" between two tables (BD forms) importing the same id data into the top master key field and matching data in the child id field would result in a automatic link of related data.
    With BD you have to create these links as there are no external key fields. If I understand correctly each record in BD stores a list of it "own record numbers" internally that it is related to - not what you enter directly or import like the examples mention above.

    Now if you create each form with fields to hold these user ID's or KEY's you have some thing to work with that identifies which form is potentially related to another.
    I would first query the master form data to get a record set (recent import??) or may be the all the reords if that is what you need but this may slow things down if you have thousands of records. With this recordset use a foreach record loop. Within this loop set a variable to current master ID (key) and then run a query on the related form with this variable. Now you should have another recordset (based on query) of related records. Use this recordset with another inside foreach loop with the "change relational field value" action. Script moves to next master record and repeats. Main form now should have matching child records in a many to many field.
    Regards
    David

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •