Why won't this save to primary file?

[Themis Skiathitis]

I know I'm going to feel really dumb when this gets sorted, but right now I can't figure out why the following bit of code won't save to the primary record.

This is only a bit of code to try to come to grips with XBasic (I'm the guy that used to use Dataflex)There is actually another 4000 odd lines of code to go in here but no point in doing that until I can get it save one bit of info.

The set contains 2 dbf's, formdata which is a horses formcard, and racedata which is all the individual runs that horse has had. Racedata has the horses complete history on a race by race basis with each race being one record while formcard is a summary of the horses total racing form showing total number of runs at say, a mile, 6 furlongs, heavy tracks, etc.

As new race results are downloaded each week into the racedata file, the horses form card needs to be updated by adding to the existing formcard the new details of races the horse has run in the last week.

I hope to be able to do this by running a script that will go thru and update the horses that have raced in the preceeding week and then mark the race records as being processed.

I know the code below is working to a point as I have placed trace writeln (removed now) thru it and they all seem to appear the correct number of times based on how many child records there are.

Any help of info would appreciated and thanks in advance
Greg

t1=table.open("d:a5v4racingformcard.dbf") 'Primary Table
t2=table.open("d:a5v4racingracedata.dbf") 'Child Table
t1.index_primary_put("horse")

'Get pointer to primary table of form.set
tbl = table.current()

'Get pointer to child table in the set
table.get("racedata")
t2.fetch_first()
WHILE .NOT. t2.fetch_EOF()
t1.change_begin()
t1.firstuprun+1
t1.change_end(.T.)
t2.fetch_next()
END WHILE
t1.close()
t2.close()

[Stan Mathews]

RE: Why won't this save to primary file?

I'm not sure I'm following all your script, but

table.get("racedata")

is incorrect. The examples in the documentation show

.table_get() and .table_get(), not the way you are using it.

t1.firstuprun+1 -----what does this do? Do you mean

t1.firstuprun = t1.firstuprun+1

[Stan Mathews]

RE: Why won't this save to primary file?

Sorry, my pasted in text didn't show correctly.

The examples in the documentation show

"index".table_get() and "fieldpointer".table_get(), not

the way you are using it.

Stan

[Themis Skiathitis]

RE: Why won't this save to primary file?

Sorry Stan, there were some errors there probably caused by me changing things in order to try and get it working. It should have looked like this;

t1=table.open("d:a5v4racingformcard.dbf") 'Primary Table
t2=table.open("d:a5v4racingracedata.dbf") 'Child Table
t1.index_primary_put("horse")

'Get pointer to primary table of form.set
tbl = table.current()

'Get pointer to child table in the set
child=table.get("racedata")
t2.fetch_first()
WHILE .NOT. t2.fetch_EOF()
t1.change_begin()
If t2.dist

[Stan Mathews]

RE: Why won't this save to primary file?

Greg,

Try changing

t2.fetch_first()
WHILE .NOT. t2.fetch_EOF()
t1.change_begin()
If t2.dist If t2.dist
t1.a1000run = t1.a1000run+1
End If
t1.change_end(.T.)
t2.fetch_next()
END WHILE

I don't know why the "if condition then do this
didn't generate an error.

Let me know how this works.

[Themis Skiathitis]

RE: Why won't this save to primary file?

thanks very much Stan, that did the trick. Problem now is that it saves all the records entered against the first record in t1.

For example, I created 3 records in racedata, 2 records for horse A and one for horse B.

When i run the script it saves all the info from all 3 records on the record for horse A only. It is not finding the next matching record. I assumed that because of the lookup it would find the corresponding record in t1 and put the information in that record rather than the first record in the db.

As you can imagine, the update that is to occur each week has to update the records for approx 4000 horses that race each week. Because the race records are downloaded from the race clubs, this script needs to update all the horses sequentially.

Time to do some more reading methinks :)

Thanks again

Greg

[Tom Cone Jr]

RE: Why won't this save to primary file?

Greg,

I think I see why the script behaves as you describe.

In pseudo code your script does this:

1) opens the two tables
2) fetches first rec in t2
3) steps through all recs in t2 and for each one
3a) updates the value of the a1000run field in the current rec in table t1.
4) closes the two tables


The record pointer in table t1 never moves.
Table t1 is updated regardless of whether the 'horse' field is the same in both tables.


In pseudo code I'd fix your code this way:

1) open both tables
2) fetch the first rec in t1; assign value of horse to a variable
3) step through all recs in t2 and for each one
3a) if the value of horse in the current t2 rec matches the variable, update the a1000run field, otherwise
3b) skip it
4) loop back to next rec in t1 and step through the t2 table again.
5) close the two tables.


As you can see this approach will be pretty slow, since all recs in t2 get checked for each horse in t1.

Perhaps a better approach would be:

1) open both tables
2) set the index to t1 to 'horse'
3) fetch first rec in t2
4) loop through all recs in t2 and for each
4a) examine the value of horse
4b) use value of horse to find the rec in t1 which has same value in horse field
4c) if found is successful, update value of a1000run field in t1
4d) if not, skip it, or possibly write it to an errata file
5) close both tables.

Hope this helps.

-- tom

[Finian Lennon]

RE: Why won't this save to primary file?

Greg:

I got to thinking how flexible A5 is and how there always seems to be several ways to accomplish the same goals. I also became suddenly curious as to the relative performance of the methods I show below. It's easier for me to do this stuff on tables I already have lying around so I hope you'll forgive the need for you to extrapolate from my case to yours, however, the processes involved are identical, so I don't think that should be too hard.

The key to writing code for what you want is to first decide WHERE the the code will run from and then program accordingly. There are benefits to doing things one way or another depending on the the size and nature of the application, number of likely users, menu structure and so on.

Below are three approaches to your problem. They each produce the same result but some work faster than others. They are designed to run either from a menu form that is not based on either of the tables in the set or from a form based on the set, which sums up the likely possibilities.

BILLTOTAL.SET is a set with the parent, billhead.dbf, linked one to many to proced.dbf on the invoice number. The idea is to fetch through all the bill headers and summarize the fees, payments and adjustments which are stored in the fee field (coded according to their type by putting a value in the pay_type field) and write the values into corresponding summary fields in the parent table. I put 3 sets of summary fields in billhead.dbf so I could easily verify that the three scripts produced the same results. There were about 200 records total in billhead.dbf and about 1000 records total in proced.dbf

Notice that when I open the set for the fetch, I use table.get() to get the pointer to the child records (the table is already open when the set is opened), otherwise I use table.open().

'=====
'1 OPEN SET METHOD RUN FROM MENU TABLE
dim fee as n
dim pay as n
dim adj as n

fee=0
pay=0
adj=0

t1=table.open("billtotal.set")
t1.index_primary_put("Inv_Number")
t2=table.get("proced")
t1.fetch_first()
while .not. t1.fetch_eof()
t2.fetch_first()
while .not. t2.fetch_eof()
if t2.pay_type="F" then
fee=t2.fee+fee
end if
if t2.pay_type="P" .or. t2.pay_type="C" .or. t2.pay_type="R" .or. t2.pay_type="I"
pay=t2.fee+pay
end if
if t2.pay_type="A"
adj=t2.fee+adj
end if
t2.fetch_next()
end while
t1.change_begin()
t1.fee_total1=fee
t1.pay_total1=pay
t1.adj_total1=adj
t1.change_end(.T.)
t1.fetch_next()
fee=0
pay=0
adj=0
end while
ui_msg_box("Bill Recalculation", "Completed")

'2 OPEN TABLE METHOD FROM MENU TABLE
dim fee as n
dim pay as n
dim adj as n
dim inv as c

fee=0
pay=0
adj=0

t1=table.open("billhead.dbf")
t1.index_primary_put("Inv_Number")
t2=table.open("proced.dbf")

t1.fetch_first()
while .not. t1.fetch_eof()
inv=t1.inv_number
query.filter="inv_number='"+inv+"'"
idx=t2.query_create()
recs=idx.records_get()
t2.fetch_first()
while .not. t2.fetch_eof()
if t2.pay_type="F" then
fee=t2.fee+fee
end if
if t2.pay_type="P" .or. t2.pay_type="C" .or. t2.pay_type="R" .or. t2.pay_type="I"
pay=t2.fee+pay
end if
if t2.pay_type="A"
adj=t2.fee+adj
end if
t2.fetch_next()
end while
t1.change_begin()
t1.fee_total2=fee
t1.pay_total2=pay
t1.adj_total2=adj
t1.change_end(.T.)
t1.fetch_next()
fee=0
pay=0
adj=0
end while
ui_msg_box("Bill Recalculation", "Completed")

'3 TABLE CURRENT METHOD RUN FROM FORM BASED ON SET
dim fee as n
dim pay as n
dim adj as n

fee=0
pay=0
adj=0

t1=table.current()
t1.index_primary_put("Inv_Number")
t2=table.get("proced")

t1.fetch_first()
while .not. t1.fetch_eof()
t2.fetch_first()
while .not. t2.fetch_eof()
if t2.pay_type="F" then
fee=t2.fee+fee
end if
if t2.pay_type="P" .or. t2.pay_type="C" .or. t2.pay_type="R" .or. t2.pay_type="I"
pay=t2.fee+pay
end if
if t2.pay_type="A"
adj=t2.fee+adj
end if
t2.fetch_next()
end while
t1.change_begin()
t1.fee_total3=fee
t1.pay_total3=pay
t1.adj_total3=adj
t1.change_end(.T.)
t1.fetch_next()
fee=0
pay=0
adj=0
end while
ui_msg_box("Bill Recalculation", "Completed")
'=====

In terms of speed, scripts 1 and 3 do essentially the same thing (except they start from different places) and run in about the same time - maybe 3 seconds. Script 2 takes about 3X as along to run.

If you try to run script 1 (which opens the set) from a button based on the set form it will not work because the table.open(set) method will fail because the set tables are already open.

Periodically I like to get these things straight in my own head ....

Finian

[Peter.Wayne]

RE: Why won't this save to primary file?

You don't have any relationship established between the 2 tables.
If you are running the script from a form, then you should not use
table.open() at all, just use table.get(), and then the
set relationship will be respected by the script.
As it is, you have opened 2 new pointers to the
tables, and the new pointers do not have any linking relationships
defined.
This will be clearer to you when you receive the "learning xbasic" book.

[Themis Skiathitis]

RE: Why won't this save to primary file?

Ok where to start? Well I seem to have gotten the script (which is run as an operation from the control panel) to do all that I want it to do with the minor exception of it updating only the one record :)

When I adapted the script as suggested by Finnian (because the applications are virtually identical, invoices have items and horses have races) I got the first record updated (there are 2 horses in the horse dbf and 6 records in the race dbf) but all fields updated read 99 in the races run field, the races won field, etc.

As you can appreciate its hard to get 99 results from 6 races but somehow it managed to do it. At the same time, I am getting a lot of errors in the script of fields not being the correct subelement type, etc.

Silly thing is that even though there may be 4 or 5 lines of code relating to those fields, its picks the 3rd one for instance and says its the incorrect type.

If anyone is interested in having a look I have included a zip file with all the files in it.

The Script that works is called Update Records
The script that that should work is called New Attempt
As you can see by looking at the script, only the code for processing races under 1199m is included the rest will go in when its all working right.

The set is called form and is a one-to many link between formcard and racedata.
The horse (primary) dbf is formcard
The race dbf is racedata

If you run the Update records script it will show you what the formcard should look like post processing.

At the beginning of last week I was 47, now 10 days later I feel 70! Not to mention not having any hair left :)

Once again guys, thanks for the help, it really is appreciated and if it seems that I'm a bit thick its only because going from dataflex 2.1 to A5 with a 10 year gap in between makes for a steep learning curve.

Thanks again

Greg

[Finian Lennon]

RE: Why won't this save to primary file?

Greg:

I think you're on the right track with the "New Attempt" script. The basic framework is there and it's a question of getting the details right. You're certainly trying to get a lot of lines all right out of the gate, so to speak. I'd advise cutting way back to just a few of the values until you're confident in the script logic and flow.

The most important thing you can do is to stick a debug(1) line into the script early and run the script. When the debug window pops up you can enter the names of the variables and fields in the lines below and then click through the script line line by line.

Right off it seemed obvious to me that there are some flaws in your logic in constructing the race and fin variables (among others) which will be readily apparent to you when you see the sorts of values they are being given as each record in the child table is processed. Once you get going with the debugger I pretty sure you'll be able to identify the problem areas.

Finian

[whanigsberg]

RE: Why won't this save to primary file?

Finian:

I would like to thank you for these examples which really zero in on an issue--which method to choose--which is particularly vexatious to the less experienced.

I believe I understand most of the code (which is so clear and clean) and the reasons for the choice of method as well as the changes to the code imposed by the choice of method. But I have a couple of questions.

-Am I correct to assume that the reason example 2 runs slowest is that it must explicitly query the child table ("proced") whereas 1 and 3 are able to rely on the selection of child record imposed by the set?

-In example 3, you use the table_current method to open the primary table of a set on which your form is based. But then you use table.get("proced") to create a pointer to the already-open child table. It is helpful to me to realize that when you use table.get in this way you create a pointer to the current session of the child table which means you have access to the subset of records linked to the current record of t1 (and don't have to create your own query). My question is simply, is there a reason why you didn't write,

t2=table.current(2)

or is is 6 versus a half dozen?

Thanks,
Bill

[Themis Skiathitis]

RE: Why won't this save to primary file?

ok we seem to be making some progress here! :)

The script shown below does actually do what its supposed to do but it still wont update the next record!

I have created a small set form with a button to the script thinking that this may help but alas no.

The other thing is that the script seems to go into an endless loop and has to be terminated manually.

dim dist10 as n
dim dist12 as n
dim dist14 as n
dim run10 as n
dim run12 as n
dim run14 as n

dist10=0
dist12=0
dist14=0
run10=0
run12=0
run14=0

t1=table.current()
t1.index_primary_put("horse")
t2=table.get("racedata")

t1.fetch_first()
while .not. t1.fetch_eof()
t2.fetch_first()
while .not. t2.fetch_eof()
if t2.dist = 1200.and.t2.dist = 1400.and. t2.dist 0 then
run10=run10+dist10
end if
t1.a1000run=run10
If dist12>0 then
run12=run12+dist12
end if
t1.a1200run=run12
if dist14>0
run14=run14+dist14
end if
t1.a1400run=run14
t1.change_end(.T.)
t1.fetch_next()
dist10=0
dist12=0
dist14=0
run10=0
run12=0
run14=0
end while
ui_msg_box("Bill Recalculation", "Completed")

I have tried altering the index command to t1.index_primary_put()
but still no result.

I have also removed all but the 2 records that relate to the 6 race result records but still no go.

Does the code look all right at least? As in the one I posted the other day, the code works fine but won't go over to that next record. I have a lookup field rule on the horse field from formcard to racedata. Could this be the cause?

There goes some more hair :)

[Finian Lennon]

RE: Why won't this save to primary file?

Hi Bill:

All of your assumptions are correct (I guess); method 2 is slowest because of the need to run the query each time. I used table.get() in 3 because I momentarily forgot about table.current(2) which is what I use in most cases anyway. I think that they are functionally identical.

Finian

[Finian Lennon]

RE: Why won't this save to primary file?

Hi again Bill,

I did have one oddity show up while I was doing this ....

When I saw the slower speed of method 2 I wondered if, perhaps, the query construction had spmething to do with it. Perhaps the LQO was not kicking in or something. At the same time, there had been no obvious query activity while the script ran. I decided to change the query filter from

query.filter="inv_number='"+inv+"'"
to
query.filter="Between(inv_number,'"+inv+"','"+inv+"')"

thinking I might get some performance change. I did, but it wasn't what I expected .. the script took almost 2X again as long to run about 15 seconds in total with the blue line being visible on every query. So, evidently, the LQO was in effect in the first case but not the second.

And there I left it.

Finian