Best practice record lookup or query?

[daz_racing]

Hi all,

I'm after a bit of best practice advice...

I have a set which is 1 to many (Header/Items)

On the main form I maintain the header fields and all works ok.

Also on this form I have a variable field for a barcode which is a unique identifier to the items. What I'd like to be able to do is when I scan a barcode is do a lookup/filter/query and open an "Item" form with the form waiting for input from the user in the correct mode.

The correct mode is determined as follows;
- "enter new item" mode if the barcode was not found.
- "amend existing item" mode if the barcode was found.

What's the best practice way to locate the required record in this type of scenario?


Thanks in advance for any help...

[Mike Wilson]

Re: Best practice record lookup or query?

Darren,
I "probe" tables alot with branchpoint if-then or Select-case statements. I use a5_get_records_in_query() because I generally have local variables and varying and somewhat compound/complex filter expressions needed for the query. It would generally look like this for you:

dim vbarcodescanned as C

if a5_get_records_in_query(tablename,"barcodefield = "+s_quote(vbarcodescanned ) = 0 then
  >> open form in entry mode
else
  >> open form filtered 
end if

Because this a very specific event with a simple filter expression, you could set up and index for the barcode field and use dbcount() instead of A5griq. Look into it in the documentation.

[daz_racing]

Re: Best practice record lookup or query?

Thanks Mike - I'll look into this technique...

[daz_racing]

Re: Best practice record lookup or query?

Mike,

When I've determined what mode to open the form in (using the a5_get...), what would be the most efficient way of getting to the record represented by the barcode? Would it be a query or a filter (or other)? Also, what event would you use on the form that is being opened to locate the record to be amended - OnActivate?

Thanks...

[Tom Cone Jr]

Re: Best practice record lookup or query?

I think using an indexed find by key or using a query that can use a permanent index via lightning query optimization will be pretty much six of one or half a dozen of the other. I would not embed a filter into the called form's layout.

I would not use any events on the called form to locate the record. Instead I would open the form hidden, do the find or query, and then show the form. All work would be done from the script that opens (loads) the called form.

[daz_racing]

Re: Best practice record lookup or query?

Hi,

Ok, I'm a bit stuck with this and I've got a bad case of Help file overload

I'm still trying to load a form in the correct mode with the correct record displayed on the form once it's displayed.

In the following code, ".fetch_find()" is locating the correct record number and the Quick_Prod_Variants form is being displayed, but it doesn't display the correct record. It actually displays the 1st record in the product_variants table and not the one that was found in the index.

Here's the code I have in the OnChange event of a field on the parent form and I'm trying to open the Child form with the record that was located by the "...fetch_find" - any ideas?

Thanks in advance...

DIM Shared varP_Quick_Prod_Variants as P
DIM Shared varN_recno as N
DIM varC_table_name as C
DIM varC_index_name as C

'Sep-up constants
varC_table_name = "product_variants"
varC_index_name = "Pv_Barcode"

'Open table
varP_Quick_Prod_Variants = table.open(varC_table_name,file_rw_shared)

'Select the required index to use
varP_Quick_Prod_Variants.index_primary_put(varC_index_name)

'Get the record number of the required record
varN_recno = varP_Quick_Prod_Variants.fetch_find(v_barcode_int)

'Decide what mode to open the form in
if (varN_recno > 0) then
'Record exists
form.load("Quick_Prod_Variants2")
varP_Quick_Prod_Variants.fetch_goto(varN_recno)
varP_Quick_Prod_Variants.change_begin()
Quick_Prod_Variants.show()
Quick_Prod_Variants.activate()
else
'Record doesn't exist
form.load("Quick_Prod_Variants")
varP_Quick_Prod_Variants.enter_begin()
Quick_Prod_Variants.show()
Quick_Prod_Variants.activate()
end if

END

[MikeC]

Re: Best practice record lookup or query?

Darren,

This sort of popped out at me--could be the problem?? The "2" indicates this is the third instance of this form but you have only opened one---makes me think you have copied some of this script from Action Scripting and when you did (if you did!) it included the "2" in the script (and rightly so if 3 forms were open). You also have a "show()" function but don't see where you opened the form as hidden....

'Decide what mode to open the form in
if (varN_recno > 0) then
'Record exists
form.load("Quick_Prod_Variants[COLOR="Red"]2[/COLOR]")
varP_Quick_Prod_Variants.fetch_goto(varN_recno)

[daz_racing]

Re: Best practice record lookup or query?

Thanks for replying Mike... sorry, that's my fault, that was just a test I was doing with a simplified version of the child form, which came up with the same results - ie; it still displayed the 1st record in the child table.

Does it look to you like I'm on the right track with what I'm trying to do?

Also, not sure if it makes a difference, but the parent form is based on a parent/child SET and the child form being opened by the OnChange event code is also based on the same SET.

[Tim Kiebert]

Re: Best practice record lookup or query?

'Decide what mode to open the form in
if (varN_recno > 0) then
'Record exists
form.load("Quick_Prod_Variants2")
varP_Quick_Prod_Variants.fetch_goto(varN_recno)
varP_Quick_Prod_Variants.change_begin()
Quick_Prod_Variants.show()
Quick_Prod_Variants.activate()
else
'Record doesn't exist
form.load("Quick_Prod_Variants")
varP_Quick_Prod_Variants.enter_begin()
Quick_Prod_Variants.show()
Quick_Prod_Variants.activate()
end if

END

Darren,

You are mixing table pointer methods and form methods The form that you have opened has a different session to the table you have opened. and so is not seeing what is happening there. Keep every thing at the form level in this case.

 'Decide what mode to open the form in
if (varN_recno > 0) then
    'Record exists
    'No need to but form into change mode.
    'That happens when you start editing a field.
    [COLOR=Black]frmP = form.load("Quick_Prod_Variants")
    frmP.index_set([/COLOR]varC_index_name[COLOR=Black])
    frmP.find([/COLOR]v_barcode_int[COLOR=Black])
    [/COLOR][COLOR=Black]frmP.show()
    frmP.activate()[/COLOR]
else
    'Record doesn't exist[COLOR=Black]
    frmP = f[/COLOR][COLOR=Black]orm.load("Quick_Prod_Variants")
    frmP.new_Record()
    [/COLOR][COLOR=Black]frmP.show()
    frmP.activate()[/COLOR]
end if

Since you have an index available you could replace this

DIM Shared varP_Quick_Prod_Variants as P
DIM Shared varN_recno as N
DIM varC_table_name as C
DIM varC_index_name as C

'Sep-up constants
varC_table_name = "product_variants"
varC_index_name = "Pv_Barcode"

'Open table
varP_Quick_Prod_Variants = table.open(varC_table_name,file_rw_shared)

'Select the required index to use
varP_Quick_Prod_Variants.index_primary_put(varC_index_name)

'Get the record number of the required record
varN_recno = varP_Quick_Prod_Variants.fetch_find(v_barcode_int)

with

 DIM Shared varP_Quick_Prod_Variants as P
DIM Shared varN_recno as N
DIM varC_table_name as C
DIM varC_index_name as C

'Sep-up constants
varC_table_name = "product_variants"
varC_index_name = "Pv_Barcode"

'Decide what mode to open the form in
if exist(v_barcode_int,varC_table_name,varC_index_name) then
etc......

[MikeC]

Re: Best practice record lookup or query?

awww..thank you Tim--just got back on and was checking out some of the same things you just pointed out---but am glad you did as although I can do most things myself I still many times have a hard time explaining them to others.

[Tim Kiebert]

Re: Best practice record lookup or query?

awww..thank you Tim--just got back on and was checking out some of the same things you just pointed out---but am glad you did as although I can do most things myself I still many times have a hard time explaining them to others.

As we say down here "No worries, mate"

[Mike Wilson]

Re: Best practice record lookup or query?

mike, tim,
at least one of you (that would be the one this side of GMT) needs to go to bed..oh me too I guess...

[daz_racing]

Re: Best practice record lookup or query?

Thanks guys... I'll try it out ASAP...

...and looking at my posting time, I need to get some sleep as well! ;)