Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    37

    Unanswered: Imports with pre-defined relational fields

    I took a firebird database and combined it a single excel file.

    It was a complete mess but I managed to get it right.

    In BD I created the forms with relations but when I import them excel file it does not fill the relation fields.

    Is there any way to force BD to fill the relations with an import

    Thanks in advance guys

  2. #2
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Not sure what condition the excel data is in if multiple tables are combined in to one excel sheet but yes it is possible to do what you want provided there are unique keys in in the data and each row has related data (far from certain about this).
    You would import required coloums/fields in to appropriate forms. For each row and then run a script(s) from main (master) form through each record - run a query on related form to get records with matching keys - create record set from query - with a for each loop - use BD's add related action and then move to next main record and repeat above.

  3. #3
    Join Date
    Jan 2012
    Posts
    37
    Ok tamcind that is way above my knowledge of BD

    I think I might have to go the other way and drop the relations completely

    Thanks for the advice again

  4. #4
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Thats ok but don't rule out the power of BD for these problems - provided speed is not required some quite involved processing of data is possible (a slow data import is ok if used for a one off job).
    If we could see a sample of your excel data with description of what columns belong to which form, we may be able to be more confident and specific.
    IE: If it was a customer - order relatation I would expect each row of excel to contain duplicate data about customer for each order made by customer ( the duplicated records in resulting customer form would need removing but possible to do - I think there is a sample file on BD support page).
    Just try to avoid any duplicated data as this will be a nightmare to maintain.
    Regards
    David

  5. #5
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    cplanet,
    tamcind assisted me with a similar question I had previously (http://www.dbforums.com/brilliant-da...-question.html). His solution worked flawlessly and was quick and simple. I did successfully use the import function to mass download from EXCEL to Brilliant Database as long as the number of records was less than approximately 33,000... after that, the file would error out when I tried opening it. I am slow, really slow when it comes to Brilliant Database but I was able to download from EXCEL on my first attempt following David's recommendations!

    I do have another method of downloading that I use but it is (purposely) much slower because data validation occurs via scripting with each entry.
    Daryl G

  6. #6
    Join Date
    Jan 2012
    Posts
    37

    Sample DB

    Ok attached is the sample DB cleaned with 1 record

    QUICK BACKGROUND
    This will be used to manage wireless repeaters, towers and clients and its working very well, I am testing the SMS API which will allow us to SMS people affected in certain sectors etc.

    The scripts for the executables will not work because I cannot figure out to tell BD to run the files in the current folder, so I pointed it to a dedicated folder which will cause problems later on.

    You will notice that I made a duplicate copy of the forms to show the relationships .

    If I understand correctly I need to make some sort of query in the form that has no relation and let BD copy this to the form with the relation field.

    Tamcind, BD is very powerful but still a bit tricky for me, could I please ask you or some other brainy people to help point me in the right direction.

    It so simple in Access top fill a relational table but Access sucks at doing external things

    Thanks in advance
    Attached Files Attached Files

  7. #7
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi cplanet
    I am not that familiar with network issues but your ping and Winbox buttons do seem to open their selected program and receive parameters from the current record ( connection refused or not found reported back) but this is something I would let others explore.
    In reference to your forms, I can't find any unique ID fields (enforced by system properties for a field). I would add possibly 2 fields - an auto ID type (number) and a unique "human" field of your choosing. Also a key field (often called a foreign key in some databases) in any form that will be a child of another form (related). In many databases if a master key matches a foreign key then the relationship is automatically be established when data is imported in to each form (or table) - forms are related by these fields. BD does not work this way, it stores keys internally (might be wrong but I think this called multivalue) and you must "tell it" which records are related. They only become visible with "record sets" and must be created by BD itself through scripts (user or internal). For this reason you need key fields to run scripts against.
    Regarding your text files - these are empty on my computer - nothing to analyze ? These would need a unique id entry and if related a match key - otherwise the scripts have no reliable way of knowing what belongs to what.
    Some what busy at moment but if you have suitable text files with explanation of how fields relate, I would try to make a basic script for 2 related forms with data from 2 imported files.
    Regards
    David

Posting Permissions

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