Fastest Method of Adding New Record to A form and Posting Data to New Record

[nigeldude]

Seeking assistance from the A5 gurus.

I have a POS software that I've been developing over the past 5 years, and I still can't find a method of adding new records to my invoices without a noticeable delay.
for much of this time, I thought the delay was related to the querying of the desired product after scanning. Now, I realize that the query is run fast enough to be considered acceptable. The real delay is, the time it takes to add a new record to the "Invoice Items" table and post the data to it from the "Products" table. Below is the code I'm using:

Invoice form is based on a set comprising of:
"Invoice Header" table
"Invoice Items" table
other tables like: Company, Customers, etc.

Form name: POS
Invoice Items browse: Invoice_items_b



:POS:Invoice_items_b.new_record()
:POS:Product_id.value = eval("products->Product_Id")
:POS:Item_num.value = eval("products->Item_Num")
:POS:Price.value = eval("products->Un_Price")
:POS:Pricewithtax.value = eval("products->T_Price")
:POS:QUANTITY.Value = eval("If(Var->vReturnMode = .F., 1, -1)")
:POS:Qty_before.value = eval("products->Qty_Onhand")
:POS:Invoiceitems_dept_id.value = eval("products->Dept_Id")
:POS:Invoiceitems_department.value = eval("products->Department")
:POS:Invoiceitems_shift_id.value = eval("Var->Current_Shift")
:POS:Invoiceitems_cashier_id.value = eval("user_name()")
:POS:SOLD_FROM.Value = "Shelf"
:POS:Invoiceitems_date.value = eval("date()")
:POS:Invoiceitems_time.value = eval("Now()")
:POS:Invoiceitems_status.value = eval("'Open'")
:POS:Invoiceitems_company_id.value = eval("'0'")

Basically, each time a product is scanned, or manually entered by the cashier, the "Product_Lookup" form query the desired product, once found, a new record is added to the invoice and the above info is added to this new record before the software waits for further instructions from the cashier.
The time it takes to complete the process is always longer than the time it takes the cashier to put the product down, pick up the next product and scan it. When I go to other stores, I see their softwares move faster than the cahier, but, my cashier is always waiting on my software to complete.

Help please!!!!!

[Ronald Anusiewicz]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

Just some thoughts: is there an index for the table that is used for the product lookup?
I do not believe you need to be using the eval() function which might be slowing things down.
You appear to be working with the form. I'm thinking that maybe if you did all your computations using xbasic
and manipulated the underlying tables, then refreshed your form might not be faster.

[Ted Giles]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

Can some of the data be held as variables?
Examples = Shift, Date, Company Id etc?
That might speed things up if there are some defaults like these.

[milesjg]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

Nigel,
In addition to the use the underlying tables and refreshing try the <tbl>.batch_begin()/end() method. This might
not be appropriate depending on your network considerations but it can make a MAJOR difference.

[MoGrace]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

To utilize the tables that are open with the form for data entry, take a look at change_record() and enter_record()

[Stan Mathews]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

To speed up data entry I would try a simpler set than described. Invoice_header one to many to invoiceitems to products. The original set can still be used for reporting. If that approach does speed things up I would then try another set without products as a grandchild and use lookupc() and lookupn() to derive the appropriate values from the product table.

[nigeldude]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

Thanks to everyone for their suggestions. I will proceed with all suggested, and update you afterwards.
Thanks again.

[Ronald Anusiewicz]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

Nigel, I actually did something similar quite a few years ago. After looking at the app, it turns out what I did most follows Stan's suggestion. I have a header table and a one-to-many items table. I did use lookup functions to populate the items table. The app is far too complex, too many moving parts, for me to describe hear. But there is no lag.

[nigeldude]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

I think maybe I have too many calculations going on on the form.

[Ronald Anusiewicz]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

Nigel,

Not sure if this could help but you may want to try this. It may show you a bottleneck.

PROFILER_BEGIN()

Description

Insert the PROFILER_BEGIN() command in an Xbasic script at the point that you want to begin profiling a script. Insert the PROFILER_END()command at the point that you want to end profiling a script. When the PROFILER_END() command is encountered, Alpha Five dumps a report to the Trace window showing each line in your Xbasic script that was executed, the number of times it was executed, and the time taken to execute the command.

Profiling a script can help you determine which parts of a script might be taking a long time to execute. It can also help you debug your script because in complex scripts with lots of loops and branches, it can be difficult to see exactly which lines in your script actually get executed. The profiler report shows you what got executed.

Examples

Assume that you have created this script:

profiler_begin()
t = table.open("customer")
t.fetch_first()
while .not. t.fetch_eof()
    t.change_begin()
    t.Company = upper(t.Company)
    t.change_end(.T.)
    t.fetch_next()
end while

t.close()

profiler_end()

After running this script, the Trace window will show:

Script profiler
Line Tot Time Cnt Source
========================================================
1 0.014018 1 t = table.open("customer")
2 0.000228 1 t.fetch_first()
3 0.000256 1 while .not. t.fetch_eof()
4 0.008717 63 t.change_begin()
5 0.009599 63 t.Company = upper(t.Company)
6 0.240749 63 t.change_end(.T.)
7 0.007564 63 t.fetch_next()
8 0.004301 63 end while
9 0.001136 1 t.close()
=======================================================

[DaveM]

Re: Fastest Method of Adding New Record to A form and Posting Data to New Record

Just a thought.
I do calcs on automotive finance that has to be to the penny. The form I use in all cases are made up of variables. Those vars are then used in scripts and UDF's for calculating and rendering answer vars which are displayed on the form and written to underlying tables in a set. After trying this with table fields and having speed issues, the variavles used as fields on the form were like lightning.