Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2

    Unanswered: 'Best Practices' sticky thread

    This is a request for the guru's here and our favorite administrator, Pat Phelan. Many of us novice users are making some critical mistakes early on in our project. These mistakes do not become evident until the project gets much larger in size. As a recent example of something wrong I did was using Recordsets to store massive amounts of data. Early on, while the data set was much smaller, it worked fine but when I finally received the whole three years volume of data, things went askew and I had no idea why.

    James Tubbritt pointed out that Recordsets were never designed to hold that much data yet there was nothing in the Brilliant Database User's Manual that makes any mention of this. After this discovery was made, ferslash pointed out that this answer possibly solved some of his previous issues. Because this issue was unknown to me (and apparently others), it necessitated redoing volumes of scripting. James undoubtedly knew the solution because he, too, found out the hard way.

    Because of the absence of actual help from Brilliant Database, if there was a small repository of 'best practices' here on dBforums it could get folks started in the right direction.. and save countless hours of unnecessary frustration and possibly cause the community to grow in size.

    So, I am going to beg our administrator Pat to start a 'sticky' thread that contains just 'best practices' and I request that James write a short narrative about avoiding the usage of Recordsets to hold large volumes of data for the initial post. Why should we all have to repeat the same mistakes that those more 'seasoned' and experienced individuals have already made?
    Daryl G

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ask and ye shall become "stuck"!

    Stay tuned, I have some ideas that I need to explore that may be even more helpful but they will take time/work (possibly 90+ days) that I don't have right now.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    226

    Use queries in scripts to locate records, not loops

    One of the key best practices is that within scripts you should wherever possible use a pre-defined query to search for records rather than looping through records in a recordset. For example, I have a script that imports sales data from a csv file. When I do the import I need to know whether the customer already exists in my database. Because if they don't I will need to create a new customer record before continuing. In my case each customer has a unique e-mail address.

    The optimal way to do this is:

    1. Define a query that will search my customers to see if there is already a customer with an e-mail address that matches the current sales record being imported. A variable defined in my script will be used for the "Text Formula" entry in the query.

    2. Now in my script all I need to do is add an If Rule and set the "Field to Check" to a Formula. I define a Math Formula and use the Data Function: "Number of Records (from query)". For the query I select the query previously defined in step 1.

    So the actual code line I would use to check if that customer currently exists is:

    If 0 = "[%queRecN(1021)]" then

    The number 1021 is the internal number that BD has assigned to that query. If this is true then the customer doesn't exist and I can go ahead and create a new one. The script will carry out this check for each sales record as it is imported.


    The sub-optimal way to approach this would be:

    1. Create a recordset variable containing all my customers
    2. Loop through each customer one at a time
    3. For each customer check if their e-mail address matches the e-mail address associated with the new sales record being imported. Only drop out of the loop if a match is found.
    4. Repeat from (2) for every sales record being imported.

    Later in my script I need to assign the sales details to a customer (who will now exist, even if they didn't before). Again, I use a query, this time to add the matching customer to a recordset variable:

    [$current_customer] = Records from Query("FindMatchingCustomer")

    In this case, I used a pre-defined query that searches for the customer with a matching e-mail address.

    In my script the recordset variables I use only ever contain a single record. I have a recordset to hold a new contact if I need to create one, and I have a recordset to hold a matching contact so that I can assign the sales details to that contact.

    I hope that is of some help. It's just an example of my approach.


    Matty
    Last edited by matty1965; 06-28-14 at 19:36.

  4. #4
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2

    Many-to-Many Relational Field Relational Set

    Quote Originally Posted by matty1965 View Post
    I have always assumed that you need a new Set if you have more than one many to many relationship between the same record types/forms.

    Matty
    Quote Originally Posted by Tubbritt View Post
    Yep, the "relation set" must be unique if you have more than one many to many on the same form.

    Regards
    James.
    This particular issue caused me many problems before so I think that it is certainly worthy to be in the best practices section. It is an easy thing to correct IF you know what you are looking for... but I had no knowledge of this important fact - matty1965 and James are spot on with their recommendations!

    Problem: There was volumes of blank lines appearing in my Many-to-Many Relational Field.

    Solution: It is IMPERATIVE that each MANY-to-Many Relational Field have unique Relational Set IF multiple Many-to-Many Relational Fields are utilized on a single form. Changing the Relational Set for each Many-to-Many Relational Form to a unique value cleared up this issue. See attached screenshot below.
    Attached Thumbnails Attached Thumbnails relational set.jpg  
    Daryl G

  5. #5
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    Developers need to exercise very good caution about trying to use the 'Delete Record(s)' command. In my attempting to use that command, via query scripting, it deleted the wrong records... repeatedly! I tried this on several different occasions only to discover that the 'Delete Record(s)' command DOES NOT WORK PROPERLY. I only attempted this in v10.5 so I do not know the behavior in v10.54.

    Rather than use the 'Delete Record(s)' command, use the 'Move Record(s) command instead and sent the unwanted files to the 'Recycle Bin'. Once in the 'Recycle Bin', these records should be able to be cleanly removed. Below is a thread that explains what was attempted the the results attained.

    Synopsis: Do not use 'Delete Record(s) command - use 'Move Record(s)' instead.

    http://www.dbforums.com/brilliant-da...scripting.html
    Daryl G

  6. #6
    Join Date
    Jun 2012
    Posts
    163
    Provided Answers: 1
    Correc use of combo boxes with pre loaded values

    When you use combo boxes, and you pre-load values on them, the first value that you have to add to your list of values is " " (an empty space, or just a "enter" stroke.

    so if you have three values for instance
    A
    B
    C

    you have to write this list

    A
    B
    C

    (the first row empty)

    This will save you hours of frustration, for some reason, when you involve a combo box value in a script, and the first row of the values is not empty, brilliant tend to put your first value, in this case "A", insted of the value that you need.

    An like many bugs, this behavior is not constant... but happens, Daryl from this forum had the same incident.

    so...

    consider it

    best regards

    fer

  7. #7
    Join Date
    Dec 2012
    Location
    Huntsville, AL
    Posts
    379
    Provided Answers: 2
    I discovered another issue yesterday that has alluded me for some time. I was using a checkbox (referred to as a 'Flag (yes/no) field' in the Form Editor) to define the status of people. If the checkbox was checked, a value of '1' appeared in that field and that worked just as it was supposed to. When I ran some queries, people that did not have the checkbox checked were sometimes appearing in the results - this was part of my frustration with Brilliant Database since results were given that simply were incorrect. I modified my scripting to prevent this odd and invalid procedure from occurring. As I was searching for another issue yesterday, I came across the actual cause: if a person did not have this Flag field checked the value was empty (this makes sense because it was never activated); however, if the field was checked and then unchecked the field value STILL remained blank. This did not seem like too much of a problem until I discovered that my scripting was still allowing the blank values to appear in the results even though the scripting strictly specified that only those that had a value of '1' (i.e., checked) would appear. The fact that Brilliant Database still permitted this is troubling and can cause developers much grief!!!

    To fix this issue, I just added some basic scripting to the 'On Enter' that set the field value to '0' and basic scripting to the Flag Field 'On Edit' that set the field value to '0' if the value was not '1'. This seemingly small issue's behavior caused me many hours of frustration and forced me to modify my program to prevent multiple people from being selected at one time since it permitted others, who were not of the same status, to appear in the output. It is now nice to know that the Flag (yes/no) field does NOT place a value of '0' in the field if it is deselected - this must be done with scripting.

    I suppose, in hindsight, that the actual scripting could have been modified to examine that the field contains a value of '1' AND that it is not blank but that seems like an oxymoron. Regardless, I am glad that this elusive and odd behavior has been identified and overcome.
    Daryl G

  8. #8
    Join Date
    Jun 2012
    Posts
    163
    Provided Answers: 1
    Error running applications in windows 8 and 8.1
    *********
    The proble is that if you compile your appliction, using the autobackup fuction build in brilliant checked to yes, and a directory that points to a directory in your computer, when you try to run the compiled application in windows 8 and 8.1 you will have a "run time error 13 type mismatch".
    when you re-compile your app, whitout the autobackup selectec to yes, you will have no problem at all"

    the auto-backup fuction i mention is located in file menu at file/database maintenance/auto-backup
    **********************

Posting Permissions

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