Script jams because of missing sequential nr

[werder101010]

Tim Kiebert wrote a script that worked perfect on a single table but isn't working within the set I'm using. After five hours of rebuilding databases and extensive testing I now know the problem occurs when the table does not contain a sequential adm_nr. Within my set a adm_nr is only produced after data is entered. I did explain this in an earlier post (see quote).

In my attachment you'll see the �adm hoger beroep� table contains five records: BBB 0001, BBB 0002, BBB 0003, BBB 0004 and BBB 0008. On the form is a button with a script that copy's two fields from the �adm hoger beroep� table to the "factuur" table. When standing on record 8 the script will not copy the fields and will produce an error message: �record number is past end of table�. When the records BBB 0005, BBB 0006 and BBB 0007 are added to the �adm hoger beroep� table the script will work.

Can the script be adjusted so it will work in both cases (with and without a sequential adm_nr)?

Three different table's need to share the same adm_nr. That's why I have made a fourth table that contains only one field for the adm_nr and made this table the parent in a set (auto increment is turned on). The three tables (table one, two and three) are linked as a child to the parent with a �one to one� link type.

When data is entered in the first two records of table one �adm_nr 001� and �adm_nr 002� are filled in automatically. When data is entered in the second record of the second table �adm_nr 002� is filled in. As long as no data is entered in the first record of the second table, the adm_nr wil not be produced and the record will not show at all.

Is this a reliable, efficient and straight forward way to link the three table with one adm_nr?

The script I'm using is:

topparent.commit() 'Save record in current form.

rec_num = recno() 'get record number of current record

tbl1 = table.open("adm_hoger_beroep") 'open a new instance of the same table
tbl1.fetch_goto(rec_num) 'find the current record in the new instance of the table
vCurrInv = tbl1.hb_code 'use you actual invoicenumber field here

tbl2 = table.open("factuur")
tbl2.order("f_omschrf") 'Order the table because fetch_find in the next line needs to
'have the table ordered on the field in which it is looking
'Substite your actual invoicenumber field here for table two.

If tbl2.fetch_find(vCurrInv) > 0 then 'If current invoice is found in table 2 then the record
tbl2.change_begin() 'number is returned which is greater than 0 so table 2
tbl2.F_client = tbl1.hB_CLIENT 'is put in change mode.
tbl2.change_end()
Else 'If invoice number is not found then the record number
tbl2.enter_begin() 'with the nearest match is returned but as a negative value
tbl2.F_client = tbl1.hB_CLIENT
tbl2.f_omschrf = tbl1.hb_code 'so it will be less than 0 which cause the table to be put
'into enter mode.
' and so on until you have filled all of the fields
'in the destination with values from the current record
tbl2.enter_end(.T.)
end if

tbl1.close()
tbl2.close()

[Tim Kiebert]

Re: Script jams because of missing sequential nr

The reason for the error is that you are grabbing the record number of the parent(adm algemeen) which is 8 and then searching for that record number in the adm_hoger_beroep table which does not have 8 records. It only has 5. Even if you added data to the 'missing' records in adm_hoger_beroep so that there are 8 records the 8th record in adm_hoger_beroep would not be the correct record. You still need to get the 5th record.

Using the record number is only suitable if you are using it to go back to a specific record after some other process or opening another instance of the same table. In this case you can still use the recno() as long as you get a pointer to the correct table.

Change - which is getting the record number of the parent

topparent.commit()                              'Save record in current form.

rec_num = recno()                 'get record number of current record

to

topparent.commit()                                   'Save record in current form.

tc4 = table.get("adm hoger beroep")   'this gets a pointer using the name of the table
'or
'tc4 = table.current(4)    ' this gets a pointer according to the position in the set.
rec_num = tc4.recno()    'record number of the child table

The code you are using was provided earlier not knowing any other specifics of what you are trying to accomplish. If the values that you want to add/post to the factuur table are always going to be on the form then you can dispense with getting record numbers and opening another instance of the table just to get the values. Grab the values off the form and assign to tbl2.

topparent.commit()                                      'Save record in current form.

tbl2 = table.open("factuur")
tbl2.order("f_omschrf")                                    'Order the table because fetch_find in the next line needs to
                                                       'have the table ordered on the field in which it is looking
                                                       'Substite your actual invoicenumber field here for table two.

If tbl2.fetch_find(HB_CODE.value) > 0 then                 'If current invoice is found in table 2 then the record
    tbl2.change_begin()                               'number is returned which is greater than 0 so table 2
    tbl2.F_client = HB_CLIENT.value                      'is put in change mode.
    tbl2.change_end() 
Else                                                  'If invoice number is not found then the record number
    tbl2.enter_begin()                                'with the nearest match is returned but as a negative value
    tbl2.F_client = HB_CLIENT.value
    tbl2.f_omschrf = HB_CODE.value                                'so it will be less than 0 which cause the table to be put
                                                         'into enter mode.
                                                      ' and so on until you have filled all of the fields 
                                                      'in the destination with values from the current record
    tbl2.enter_end(.T.)
end if

tbl2.close()

On another matter: Do not use spaces in the names of your tables. The quote below is from this page

Table and Field Names
Alpha Five recommends that table names, field names, and paths to your files start with a letter (A-Z or a-z) and be composed of letters, numbers (0-9), and underscore (_) characters.

[werder101010]

Re: Script jams because of missing sequential nr

Hi Keith,

I just stopped laughing after 15 minutes. Partly because with this obstacle settled I can finally start building my application and mainly because I believe your really good at what you do. Thanks for you time and contribution.

[Mike Wilson]

Re: Script jams because of missing sequential nr

Hi Keith?

[werder101010]

Re: Script jams because of missing sequential nr

Oeps, I was trying to thank Tim. So thanks again !!!