Multiple Users Running Same Post/Append Operations Between Same Tables at Same Exact Time

[Ted Giles]

You can use as many as it will allow, however having used this software for over 30 years, my experience is that the indices won't handle more than 8 char nicely.

That post is 18 years old.

[nigeldude]

All my field names are 10 characters or less.


Attached are screenshots of my "POS" form and "Product_Lookup" form. The POS form is where the cashiers work primarily, this is built on the following tables
Invoice_Header, Invoice_Items, and a few one to one tables like Customers, Company, Products, etc,

The Product_LookUp is the form that fetches the desired product and adds it to the POS form. product_LookUp is built on the Products table.

The cashiers have many different ways of entering products. In the upper left of the POS form is a field attached to Var->Product_Lookup. In this field, the cashiers may enter several different info to locate a product.
1) Scan a traditional bar code,
2) Scan an in-house bar code printed from the product's Item_Num
3) Manually type in the Item_Num
4) Manually type in a shortcode
5) Type words that are a part of the product's name (descrip_1)

After entering text into the field for Var->Product_Lookup and pressing enter, the OnKey event then activates the Product_LookUp form. When this form is activated, it runs the below codes which uses 1 of 3 queries to locate the desired product.
If it finds a single match in the "Products" table, One of two things happens.
1) If this item does not yet exist in the current invoice, the item is added with a quantity of 1.
2) if it does exist in the current invoice, it is not added a 2nd time, instead, the quantity for the existing one is incremented by "1" via the update Operation.

If the Product_LookUp form finds more that one matching item in the "Products" table, the "Product_LookUp" form freezes with all the matching products displayed and waits for user interaction.
If the Product_LookUp form finds zero matching item in the "Products" table, the "Product_LookUp" form freezes with no products displayed and waits for user interaction.

I am aware that I have a lot of "IF" and "Else" going on here, I accept they all take time, I also know that "LOOKUPC()" is faster than Query. Unfortunately, I do not know how to use LOOKUPC() in my particular case, since I never know for sure what the cashiers will enter to retrieve the desired products. Also, I want the "Product_LookUp" form to visually react to the result of the cashier's input, displaying what was found if the result is either "0" or, more than 1
And finally, according to the Profiler function, the parts that are actually taking up the chunk of the time are the actual adding and saving of records to the POS Form.


'This is the code attached to the OnActivate for the Product_LookUp form -----------------------------------------------------------------------------------------------
'Query by UPC, ALU, Item #
topparent.queryrun("( ( products->Upc = Var->Product_Lookup ) .or. ( alltrim(products->Alu) = alltrim(Var->Product_Lookup) ) .or. ( Val(products->Item_Num ) = Val(Var->Product_Lookup) ) .AND. ( ( products->Department <> "Accounts") .AND. ( products->Dormant = .F. ) ) )","descrip_1","","No","<Cross Level>",.f.)

'Query by: Multi UPC, Description
IF Count(products->Product_Id) = 0 THEN
topparent.queryrun("( containsi(upc_multi,Var->Product_Lookup ) .or. containsi(descrip_1,Var->Product_Lookup ) )","descrip_1","","No","<Cross Level>",.f.)
END IF

'End if more than 1 product is found
IF Count(products->Product_Id) <> 1 THEN
END
END IF

'Get selected Item #
DIM GLOBAL ITEM_NUM AS c
ITEM_NUM = parentform:Item_num.value

'Add product to invoice
If tablecount("invoice_items","( shift_id = Var->Current_Shift ) .AND. ( Invoice_Nu = Var->INVOICE_NU ) .AND. ( Item_Num = Var->Item_Num )") > 0 Then
a_tbl = table.open("invoice_items")
ON ERROR GOTO ERROR11092017185323974

DIM a5_operation_order as C
a5_operation_order = "recno()"
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operation_filter = "( shift_id = Var->Current_Shift ) .AND. ( Invoice_Nu = Var->INVOICE_NU ) .AND. ( Item_Num = Var->ITEM_NUM )"
query.filter = a5_operation_filter
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create("N")

update.fields = 1
update.field1 = "QUANTITY"
update.expr1 = "Quantity + 1"

a_tbl.update()

GOTO CONTINUE11092017185323974
ERROR11092017185323974:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Update Operation"+crlf()+error_text_get())
END
CONTINUE11092017185323974:
a_tbl.close()'If the Operation is run from within a Form or Browse, then refresh the window
if is_object(topparent.this) then
if topparent.Class() = "form" .or. topparent.class() = "browse" then
topparent.Refresh_layout()
end if
end if

:POS.Commit()
:POS.Resynch()
:POS.Refresh_Layout()


else
'Create new Invoice Item and enter details
:POS:Invoice_items_b.new_record()
:POS:Product_id.value = products->Product_Id
:POS:Item_num.value = products->Item_Num
:POS:Price.value = products->Un_Price
:POS:Pricewithtax.value = products->T_Price
:POS:QUANTITY.Value = 1
:POS:Qty_before.value = products->Qty_Onhand
:POS:Invoiceitems_dept_id.value = products->Dept_Id
:POS:Invoiceitems_department.value = products->Department
:POS:Invoiceitems_shift_id.value = Var->Current_Shift
:POS:Invoiceitems_cashier_id.value = user_name()
:POS:SOLD_FROM.Value = "Shelf"
:POS:Invoiceitems_date.value = date()
:POS:Invoiceitems_time.value = Now()
:POS:Invoiceitems_status.value = "Open"
:POS:Invoiceitems_company_id.value = "0"

'Set product's Tax
IF (:POS:Tax_Exempt.Value = .F.) THEN
:POS:Is_taxable.value = If(products->Tax = "Tax", .T., .F.)
else
:POS:Is_taxable.value = .F.
end if

'Save 'POS' Form
:POS.commit()

END IF

'Clear Product_lookup
:POS:Product_lookup.text = ""

'Activate 'Product_lookup
:POS:Product_lookup.activate()

[Ted Giles]

I'm still pondering the process Nigel.
One bit of processing you could get rid of is the one where a second item updates the quantity.
In our shops, the invoice has each item on it, not
3 tins of beans

More like
1 tin of beans
1 tin of beans
1 tin of beans

[Ronald Anusiewicz]

Nigel,

First, you do not say what version of AA you are using. I tested this example using version 12.4.4.3, Build 4269.

I took my POS app and removed almost everything that was not needed. In my app you can either use a bar code scanner or just type in the tag number. In this example, the tag number is a 5 digit number.

When you run the app, the item entry form opens. Mouse click on the invoice number field to activate that field. Press the PageDn key to navigate to invoice I0000002. Should be the next invoice.

Mouse click on the red “check out items” button. You will see that the Tag No field now contains a flashing cursor. Note: the field rule is set to automatically advance when the field is filled. So after typing 8 0 0 9 6 , the record will be saved and the tag number field will be empty waiting for you to enter the next item number.

You can now type in the following tag numbers. Try it.

80096
80097
80098
80124
80133
80140
80141
80148

I’m giving this to you only to show you how I am doing bar code entry. With changes, maybe this will help you.

NigelPOS.zip

Ron

[Ted Giles]

Ron, I suspect the issue is with
80096
80096
80096
In the scan list.

[Ronald Anusiewicz]

Ron, I suspect the issue is with
80096
80096
80096
In the scan list.

Sorry Ted but I do not understand your post.

[Ted Giles]

Ron, there is a check to see if the item exists, and the qty is recalculated.

[Ronald Anusiewicz]

Our POS app is checking out unique tag numbers so we added error checking to prevent duplicate check outs of the same tag number. If you want to allow multiple check outs of the same tag number, go to the CanChange event of the tag_no field and REM out the lines 17 thru 21.

Ted, is this what you referring to?

Disclaimer: Again, this is what we are using and I modified it solely for demonstration purposes of how we are doing checkouts. As this is a partial app, there could be other issues.

[FONT=Times New Roman]'Date Created: 14-Aug-2012 09:46:19 PM[/FONT]
[FONT=Times New Roman]'Last Updated: 01-Oct-2020 03:46:56 PM[/FONT]
[FONT=Times New Roman]'Created By : Ron[/FONT]
[FONT=Times New Roman]'Updated By : Ron[/FONT]

[FONT=Times New Roman]IF var->vcMode = "e" then[/FONT]
[FONT=Times New Roman]dim vcField_Value as C[/FONT]
[FONT=Times New Roman]vcField_Value = this.text[/FONT]

[FONT=Times New Roman]if isnull(vcField_Value) then[/FONT]
[FONT=Times New Roman]end[/FONT]
[FONT=Times New Roman]end if[/FONT]

[FONT=Times New Roman]dim nrecs as N[/FONT]
[FONT=Times New Roman]nrecs = a5_get_records_in_query("sold_List","Tag_No=" + s_quote(vcField_Value))[/FONT]

[FONT=Times New Roman]' if nrecs > 0 then[/FONT]
[FONT=Times New Roman]' cancel()[/FONT]
[FONT=Times New Roman]' Error_Msg("Duplicate Tag No.","This Tag No. Has Already Been Scanned") ',UI_STOP_SYMBOL+UI_OK)[/FONT]
[FONT=Times New Roman]' topparent:tag_no.value = ""[/FONT]
[FONT=Times New Roman]' end if[/FONT]
[FONT=Times New Roman]end if[/FONT]

[FONT=Times New Roman]IF var->vcMode = "e" then[/FONT]
[FONT=Times New Roman]nrecs = a5_get_records_in_query("item","Tag_No=" + S_quote(vcField_Value))[/FONT]

[FONT=Times New Roman]if nrecs = 0 then[/FONT]
[FONT=Times New Roman]cancel()[/FONT]
[FONT=Times New Roman]Error_Msg("Invalid Tag No.","This Tag No. has Not Been Entered") ',UI_STOP_SYMBOL+UI_OK)[/FONT]
[FONT=Times New Roman]topparent:tag_no.value = ""[/FONT]
[FONT=Times New Roman]end if[/FONT]
[FONT=Times New Roman]end if[/FONT]


[FONT=Times New Roman]END[/FONT]

[Ted Giles]

I get your logic Ron, but Nigels code checks for additional items with the same id and updates the quantity on each scan as far as I can see..
A redundant process?

[nigeldude]

Thanks Ronald, I truly appreciate your time and efforts to explain your approach, I do agree your invoice input is quite acceptably fast, also the flipping back through the sales history is equally fast, tremendously faster than mines. I'll see how I can best integrate your approach into my scenario.

[Terrymorgan]

Have you tried using a shadow on the cashiers pc to speed things up?

[nigeldude]

Have you tried using a shadow on the cashiers pc to speed things up?

How would "shadow" be faster than the server?

[Terrymorgan]

Shadow keeps all of the forms scripts & functions local, reducing exchanges with the server to just the data.
I have found this always significantly faster.

[Ted Giles]

It is possible to use RT on a Server station as well as the Dev version.
The restrictions are likely to be the same as if running over a network if Shadowing has been applied.
Each RT session is separate - even on a Dev server.
Not sure how you have this set up Nigel.
Do you use RT on the Server or a Dev version?

[nigeldude]

I develop at a completely separate physical location and deliver updates to the server via an external drive.
RT is used on the server PC.
The only different between the server and the other stations is that network is not needed for it to operate on the server.