Script help

[Michael Humby]

Hi all,

The following script fetches through parent records and gets values from the last child, then adds a new child filled with values from the previous. (It will eventaually increment the month)
The script runs fine unless it hits a parent with no child records, then it fails. I have tried using Scanning() function without success.

Would appreciate any advice. File attached.

Michael

tbl=table.current()
outline_level=1
tbl.fetch_first(Outline_level)
While .NOT.tbl.fetch_eof()
tbl=table.current()
tbl2=table.current(2)
outline_level=1
Am_due_pointer=tbl.get("Amount_due")
Am_due_pointer.fetch_last(outline_level)
v_month=tbl2.month
v_weeks=tbl2.no_weeks
v_year=tbl2.year
v_roll=tbl2.Roll_no
trace.writeln(v_month+str(v_weeks)+str(v_roll))
tbl2.enter_begin()
tbl2.month=v_month
tbl2.year=v_year
tbl2.Roll_no=v_roll
tbl2.enter_end(.t.)
tbl.fetch_next(Outline_level)
End while

[Michael Humby]

RE: Script help

My file has not been attache. Second try

Michael

[Finian Lennon]

RE: Script help

Assuming this is running from a form based on the set, and the name of the child is "Amount_Due", I "think" I simplified this as follows:

'=====
tbl=table.current()
tbl2=table.current(2)
outline_level=1
tbl.fetch_first(Outline_level)
While .NOT. tbl.fetch_eof()
if scanning("Amount_Due") then 'process else goto next record
Am_due_pointer=table.get("Amount_Due")
Am_due_pointer.fetch_last()
v_month=tbl2.month
v_weeks=tbl2.no_weeks
v_year=tbl2.year
v_roll=tbl2.Roll_no
trace.writeln(v_month+str(v_weeks)+str(v_roll))
tbl2.enter_begin()
tbl2.month=v_month
tbl2.year=v_year
tbl2.Roll_no=v_roll
tbl2.enter_end(.t.)
tbl.fetch_next(Outline_level)
goto next
end if
tbl.fetch_next(Outline_level)
next
End while
end
'=====

Finian

[Stan Mathews]

RE: Script help

Michael,

"hits a parent with no child records"

I know what you mean, but this situation is really

"hits a parent with one child record, with no values in the fields". All parent records in the parent table will have at least one "composite" record in the set, the fields in the child table are just blank.

All you need do is decide how to handle these, as you obviously can't fill them in with previous values. Maybe you have some defaults? You would want to test for the blank values in the set record before you enter the while loop.

Hope this helps, have a good weekend.

Stan

[Finian Lennon]

RE: Script help

Oops I dropped the : in the goto it should be next:
and you should probably not use the word next, nextrecord: maybe.

Finian

[Michael Humby]

RE: Script help

Third attempt to upload file

Michael

[Michael Humby]

RE: Script help

Finnian,

Thanks I tried your version and when I compile I get a 'Missing end while' error.

I have managed to upload my demo file if you could take a look.

Michael

[Michael Humby]

RE: Script help

Stan,

If you see my attachment file, it finally uploade, I was too impatient.

The script runs OK, but if I select a Parent and then delete all child linked to that parent the script fails. Is'nt this a 'Parent with No Child' situation?

The first child entry will always be entered manually with mainly default values, but there will be occasions when no child record has been added.

Michael

[Finian Lennon]

RE: Script help

Michael:
I'll take a look in a little while but...
it may be the "next" that is causing the problem.

Change the line to
goto nextrecord

and the next should be

nextrecord:

Finian

[Stan Mathews]

RE: Script help

I probably misspoke, I was thinking in terms of composite set records.

You could use the key_exist() function to see if there were child records in the table, and use your script if there were, otherwise skip the record.

[Stan Mathews]

RE: Script help

Michael,

I downloaded your file, deleted 1086, the second child in amount_due, opened the form, pushed the button, and had no script failure. when I re-opened the amount_due table, there was a record for 1086 with the rest of the fields blank.

Is this what you mean by the script failing?

[Finian Lennon]

RE: Script help

If you go to the button, actions, code, click "Convert to Xbasic" and insert the following code it will fetch through the parent records duplicating the last child record as it goes. If it encounters a parent with no children it skips the record. You never did specify what should happen so I assume this is what you want. If not, you would need additional code in an "else" or after the "end if".

'============
tbl=table.current()
idx=tbl.index_primary_put()
tbl2=table.current(2)
tbl.fetch_first()

While .NOT. tbl.fetch_eof()
v_roll=tbl.roll_no
Am_due_pointer=table.open("Amount_Due")
query.filter="roll_no=v_roll"
idx=Am_due_pointer.query_create()
recs=idx.records_get()
if recs>0 then
Am_due_pointer.fetch_last()
v_month=Am_due_pointer.month
v_weeks=Am_due_pointer.no_weeks
v_year=Am_due_pointer.year
v_roll=Am_due_pointer.Roll_no

trace.writeln(v_month+str(v_weeks)+str(v_roll))

tbl2.enter_begin()
tbl2.month=v_month
tbl2.year=v_year
tbl2.Roll_no=v_roll
tbl2.no_weeks=v_weeks
tbl2.enter_end(.t.)
tbl.fetch_next(1)
goto nextrecord
end if
tbl.fetch_next(1)

nextrecord:
End while

Am_due_pointer.close()
parentform.resynch()
end
'============

Finian

[Finian Lennon]

RE: Script help

Michael:

The other works but this one ties up loose ends. BTW, the scanning function does work, but in this case, setting the outline level stops it from working, and without setting the outline level the fetch doesn't advance the table pointer. This opens the child table pointer prior to the while/end while and closes it after as well as dropping the query, good practices to follw.


'======
tbl=table.current()
idx=tbl.index_primary_put()
tbl2=table.current(2)
tbl.fetch_first()

Am_due_pointer=table.open("Amount_Due")
outline_level=1
While .NOT. tbl.fetch_eof()
v_roll=tbl.roll_no
query.filter="roll_no=v_roll"
idx=Am_due_pointer.query_create()
recs=idx.records_get()
if recs>0 then
Am_due_pointer.fetch_last()
v_month=Am_due_pointer.month
v_weeks=Am_due_pointer.no_weeks
v_year=Am_due_pointer.year
v_roll=Am_due_pointer.Roll_no

trace.writeln(v_month+str(v_weeks)+str(v_roll))

tbl2.enter_begin()
tbl2.month=v_month
tbl2.year=v_year
tbl2.Roll_no=v_roll
tbl2.no_weeks=v_weeks
tbl2.enter_end(.t.)
tbl.fetch_next(outline_level)
goto nextrecord
end if
tbl.fetch_next(outline_level)

nextrecord:
idx.drop()
End while

Am_due_pointer.close()
parentform.resynch()
end
'======

Finian

[Michael Humby]

RE: Script help

Finnian,

Thanks for your script help. Both of your versions run beautiful but I prefer your second version. I shall be posting this into the Code Archives as I think it will be invaluable to others.

Just think, all those invoice apps could have a button to add a free gift item to all of their customers accounts.

Michael

[Finian Lennon]

RE: Script help

MIchael:

As a rule I would not run a global update script like that one from a form based on the set. It requires only a few minor code changes to enable the script to run from any menu form, independent of the set. If you really want to do it from the form, you should add a few lines at the start that will check to see if the form in in change or enter mode first and commit the record if necessary.

Finian