I am reasonably intelligent. I graduated summa cum laude for my undergraduate degree and, after two years of much focused work, I was awarded a master’s degree. I am saying this to establish a bit of creditability for myself, to reiterate an anomaly I have noticed several times previously, and as a preface to my next statement: queries and the associated query scripts are unreliable in Brilliant Database. Queries are one of the major points in the operation of most projects developed in this software and I have noticed before that some of the supposedly straightforward queries were sometimes giving results that I knew were invalid. For a while, I stopped using queries altogether and totally removed any references to any queries in my scripting because the results appearing were not always correct. Later, I revisited queries because there was no other means available that could potentially capture the records that I was requiring. With this new attempt I figured that, due to my relative inexperience with Brilliant Database, it was my misunderstanding that was making it appear that the results were not right (or, that I was asking the query to do the wrong thing and expecting right answers). Now, I have much more experience with this product and I can state that behavior of queries and the associated query scripting is sometimes still not correct.
My task was to identify operations done in a particular location and to delete those records from a folder. I set up this seemingly simple query to look for that field having a certain value THEN ‘execute a script for each record’ as this is supposed to execute a script for every record from the query (that script is to delete the records found in the query to the Recycle Bin) . When I run this, it appears that the query seemed to identify the records that it was supposed to but as for deleting these records, it fails miserably as Brilliant Database seemed to indiscriminately delete random records that never appeared in the query results. I have attempted this six different times and the records appearing in the Recycle Bin have no similarity of the records that were specified.
Before anyone dismisses me as ‘being the problem’, I can assure you that everything is proper on my end. I have 98,793 records in the folder that is in question (this is less than the 100,000 limit specified by Brilliant Database). The query and query scripting is exceptionality simple for this operation yet the results are utter trash… I have previously reported my disdain for queries because of sometimes really strange behavior (I would find it hard to believe that I am the only one that has ever experienced questionable/invalid results with queries and query scripting???).
I certainly wish that Mikhail would listen to the problems that the users are experiencing. With this, corrections could be made and the software could be absolutely GREAT. Right now, it would be very difficult for me to recommend this software to anyone else because of the total absence of any technical support from Brilliant Database. Couple that with the sometime bizarre behavior that I sometimes see makes me really want to use a more reliable platform.
I am really frustrated right now at this product!!!
There may be special tricks that the more seasoned people here have adopted to overcome the limitations/restrictions of Brilliant Database so, after spending several paragraphs complaining about the software and the issue that I am having, can someone explain to me how to delete records that appear in the query results?
If I had been approaching this, I would probably have created a recordset based on the query and then processed each record in the recordset, deleting them. That way handling everything within a single script.
Before I read your reply, I tested each of the delete option (auto, current record, recent query results,... etc) and NONE of them worked properly. While this operation did delete records, it deleted the wrong records (attention Brilliant Database users - be careful with that command!). Next, I figured that I had nothing to lose, so I thought I would experiment with the 'Move Record(s)' options and this worked, just as you recommended. Something is terribly wrong with 'Delete Record(s)' function as it clearly does not work properly. I would report this bug to Brilliant Database; however, based on their unfortunate history of no responses, it is somewhat of a waste (I will probably report it anyway as that is the right thing to do and just because Brilliant Database may not respond, I always strive to do what is right!).
I considered doing it with a Recordset as you suggested but I was somewhat hesitant as there are 37,213 records that need to be deleted based on the criteria. I am not aware of the actual design limits of the number of records that can be safely held in a Recordset but I thought that this amount may be exceeding those limits.
This large amount of records to be deleted is not a sign of sloppiness due to me; instead, the erroneous records were delivered to me with overlaps in the time periods covered. I was doing some additional analysis and discovered this issue and was simply trying to remove the superfluous records.
If you have the time you might like to post a cut down version of your file ( same query set up and similar record count) for members to play with. I agree that this is disappointing when you can't trust results - particularly with a delete command (needs to be bullet proof). If only BD would communicate it might be a straight forward bug fix. At present only thing we can do is test any procedure fully with real world data before we commit resources.
Did some experimenting on work arounds with this delete problem and the "move records" as suggested in these posts is likely best option but one alternative that appears to work is the "for each interval" cycle. I set a variable to the folder record count (folRecN) and used this to loop through all records with the next record action (moving to first record before loop). Inside loop had a "if" check for certain condition and when true, do a record a delete, move etc. Unlike a recordset no large lists of records stored in memory. Likey too slow for real time use but an alternative to play with - if only to prove there is more one way to get a result.
PS - have being watching this new database "My Visual database" and forum is answered within 24 hrs - why not BD- I just can't comprehend the difference.