Query on date

[Tom Cone Jr]

RE: Query on date

Scott,

Does this also mean that it fails when you format the date for your region?

i.e.

instead of: query.filter="rm_date={11/13/2003}"

you use: query.filter="rm_date={13/11/2003}"

-----------------------------------------------

I've tried to replicate the error you're seeing using the AlphaSports sample.

OPen AlphaSports.
Close the opening menu form.
Choose View, then Code Editor.

If I enter this in the Interactive Editor, and press ENTER as each line is typed, the query filter works just fine.

tbl = table.open("Invoice_header")
dim test_date as D
test_date = {01/15/2002}
query.filter = "Date = { " + dtoc(test_date) + " } "
query.option = "M"
query_order = ""
idx = tbl.query_create()
?idx.records_get()
= 2.000000
idx.drop()
tbl.close()

In this case I knew in advance that 01/15/2002
was a valid field value in two records in the
header table.

Since your script is indistinguishable I would guess
the difficulties you're getting result from:

a) differences in how the regional formatting of
your date fields are being handled internally; or

b) corruption in your form.

-- tom

[Peter.Wayne]

RE: Query on date

in the button, change the code for the query to

query.filter="rm_date=date()"

and that should work.

[scottly]

RE: Query on date

Well this gets more and more interesting.. the between dates option works when run once, but, a table re-index must be run in order for it to work twice.

To clarify, if the delete is run using the between option, then records added to the table and the delete run again (all without exiting the Form) - the second delete does not delete any records.

There is a stability issue of some sort here. I re-instated the 'invalid' field rule default of date(), and changed the query.filter back to 'rm_date=date()' and it is now working (and works when you run it twice). If I set ws_date=date() and in the filter use 'rm_date=ws_date' it does not work.. Was it a re-index that allowed it to work with rm_date=date()? I guess I will only know after further testing.

On that, the Form also has a date range purge option which runs a delete operation for a date range as input by the user. Running this operation does not delete any records if it is run after the above single date delete routine is run - but - it runs OK if I exit the Form go back into the Form and run the operation. Again to clarify, if I use the single date delete routine it deletes records for 15/11/2003 - if I then run the date range delete (an operation) without exiting the form and run it for 13/11/2003-14/11/2003 no records are deleted - even though they exist and viewable in the browse.

I suspect there is a bug here - maybe in the file system or to be more specific A5's management of the file system. I guess all I can do is bring this thread to the attention of Alpha Software and hope they will put some effort in to resolving it.

Thanks to all for your input - it is interesting that people are formulating their date based queries quite differently - and for them it works - but on our date format and with the table involved they do not.

[scottly]

RE: Query on date

Hi Pat

I think I will get them to use right click / delete on the browse in order to delete a record. For some time I thought this option was broken, until I worked out that Change, along with Delete, must be enabled in the Browse Properties in order for delete to work (if you just enable Delete and not Change, you still get the Delete option when you right click but it does nothing).

One would think that just Delete enabled would enable you to delete a browse row - but not so..

Regards,
Scott

[Tom Cone Jr]

RE: Query on date

Scott,

The sequences you describe remind me of times when I was surprised to learn that some previous process had filtered my table without me realizing it. When I ran a query (expecting it to run against all the records) it failed because the table had already been filtered in such a way that no qualifying records were 'in view' for the 'new' query to run against.

I recommend you re-set the form's primary index when the form opens, and each time it is activated. Especially, after each operation, so that the next operation will begin with 'show All' in effect.

-- tom

[Tom Cone Jr]

RE: Query on date

Scott,

If delete is not restricted, Ctrl-D will delete any row of an embedded browse.

-- tom

[Tom Cone Jr]

RE: Query on date

Scott,

I discovered just now that if you remove the ordering expression you previously embedded in the properties of your form, your query filter works fine. When the form opens the ordering expression places the table in an inverted date sequence.

I'm not sure how to fix this, but will look a bit further.

First idea is that you'll want to specify the same display sequence in the query.order as your form is using. Your current code tries to change the sort sequence in the query. This may be conflicting with the sort sequence embedded in the form.

--tom

[scottly]

RE: Query on date

Hi Tom

You may be on to something there. I will change the order sequence to match and test some more.

Regards,
Scott

[scottly]

RE: Query on date

Hi Tom

Sorry - but how do I "re-set the form's primary index" - do something on init?

Regards,
Scott

[scottly]

RE: Query on date

As it stands, using query.filter="rm+date=data()" is now working, but I have added the order command to match the form to see if that gives more stability. I did the same 'order' change to the Delete Operation (allows date range delete)which is also called from the form, however, the date range delete operation still does not work if I run the single data delete before running the date range delete operation - in such a case, an index rebuild is required before the delete operation will work.

Regards,
Scott

[scottly]

RE: Query on date

grr - i just went back to double check the order expression added to the delete operation - viz: "invert(cdate(Rm_date))+rm_racecode" and have found that it did not and will not save the order expression. I then tried just rm_date but it would not save it either. Looks very much like a bug? Can you see if your copy of A5 will save an order expression on a delete operation.

Regards,
Scott

[scottly]

RE: Query on date

some more testing - I just went through a period of time where the delete operation would not delete any records - even after re-indexing, exiting A5, re-indexing again etc still no joy.

I had to zap the table and import some new records before it would then work. Something is definitey screwed up here and it keeps leading me back to think that there is something going wrong in A5's handling of the file system..

[Tom Cone Jr]

RE: Query on date

Scott,

I'm sorry to have sent you on a wild goose chase. The form's filter is irrelevant.

Unzip the attachment to an empty folder.

I changed the query filter to base it on the character equivalent of the current date. i.e. not asking the query filter itself to convert the date value to string.

I also made provision to run the query against an unfiltered table.

Seems to be working ok.

On your second problem I also made some progress. I changed the delete race script to position the record pointer at the race preceding the one being deleted. I think the script is straightforward, let me know if you have questions. The trick was remembering that the display sequence is not in record number order. so, my script collects the record number for the row to be deleted, then moves to the previous record and collects it's record number. After the deletion the script moves the record poiner to the previous row and refreshes the browse.

Hope this helps, even with all my typos.

--tom

[scottly]

RE: Query on date

Hi Tom

Thanks for your efforts.

I have decided to stay with "rm_date=date()" in the query.filter - it seems to be behaving now - although I have no idea why it would not work previously. One thing I added was a topparent.commit() before running the Query and Delete (to make sure a record was not being edited that it subsequently needed to delete - but this was not the cause of earlier woes). I have also removed a couple of the indexes that were on the table.

It seems that rebuilds will sometimes fix the running of the Delete Operation but it is far from stable. I have taken that option out of the Form and placed it as a seperate menu option and run a rebuild on the table when it fires up - not really ideal when there is a volume of data in the table - but at least it works now most of the time.

On the record delete issue in the browse - after discovering that one must have Delete and Change enabled in the browse properties in order for a Right Click/Delete to work, I then modified the Delete Record button to do a Delete Record at the Browse level rather than the parent level and this seems to work well - the browse is refreshed without losing your current position.

I found that your solution to the browse position on Delete worked well until one clicks on another record in the browse (straight after a delete) - upon which the browse losses its position and displays from the top again - but I certainly appreciate your input and would have gone down this path if the Delete at browse level on a button did not fly.

I only wish I could nail what was causing the original query.filter issue - I hope the issue does not return with further testing!

Regards,
Scott

[Selwyn Rabins]

RE: Query on date

i took a look at the sample files and was able to duplicate the problem.

the problem seems to be related to some kind of subtle corruption in the table.

i was able to correct the problem by

1. edit the structure of the r_races table.
2. add a new temp field called "date2" (type = date)
3. do a global Update operation on r_races table and set "date2" = "rm_date"
4. edit the structure of the r_races table.
5. rename "date2" to "rm_date"

then, from the interactive window:

t2=table.open("r_races")
query.filter = "rm_date = {11/14/2003}"
it = t2.query_create()
?it.records_get()
= 169.000000

this show that it now works, where previously it returned 0 records.

how the table header got corrupted in the first place is a total mystery to me.