Help With VFP DryCleaning POS Pickup Form

[foxprox558]

Ok, So if you will take a look at the picture attached to the thread, you will see the basic layout of the pickup form

the order database is arranged to store the customer number of the customer, when you choose the customer from the top of the form, search through the orders database and find all the orders so you can select the ones you need to pick up.
I need help with putting them in this grid and having it to where you can select multiple orders and have it auto-total in the bottom text box, then save them all as paid when you push pay. This Should Be The Final Problem I'm having. Sorry for the numerous posts, but as said before, I'm kind of new to FoxPro!

 

[foxprox558]

I'm not asking the whole form, I have the whole rest of the program completed, this is merely a small piece of the myriad of the whole piece of software, and I already have a piece of this form finished. I'm just asking some guiding help on this last of the main functions that I need help with

 

[Olaf Doschke]

Well, I've seen your screenshot, yes. I may have overstated you ask for a whole form, but indeed you ask for the functionality from the point you have a choice in the combobox, that part was already given by us and so overall you asked
for the whole form logic.

You have got a comprehensive answers from me and mike already. If my solution isn't explained well enough, have a try with Mike's suggestion instead, perhaps.

It's not forbidden to have a more complex question, but this can't be solved in a normal threa, you're welcome to do as many questions as you like, but please don't revert to the inital state of having no idea without referring to any solution we already gave and you have problems with.

I have already addressed your problem with the query by giving you the possibility to identify the location of the real error, it's not in the query you address, that's for sure. I belileve you get that error message, but surely not from that query.


Anyway, the ingredients surley are
1. have a form with textbox for entry of phone or name (done)
2. have a combobox listing customers (done)
3. have a find butten listing orders of the customer choosen in the combobox. - see my answer
4. have a grid displaying the list for picking from it (done, but not yet bound to data)
5. have a total textbox (done, but code missing to fill it)
6. have a button for setting selected orders as paid (done, but no code yet)

So you see, you have at least four questions:

I) How do I bind a grid to data? How does a grid work and display data?
First answer of me was setting the RecordSource property of the grid.
Second advice to use the builder (assistent) for grids. Have you tried anything in that direction?
If not, you of course still have nothing displayed in the grid.

Also the Load event I gave you was to initialise the grid binding to an empty cursor. You only want to see orders after you chose a certain customer, don't you? If not, then remove WHERE .F. and get all data into the grid to let it show all orders initially.

II) How do I query orders of a selected customer?
The query is given.

III) How do I get a checkbox into a grid.
Again the builder may help here, actually I don't know as I do my grids manually. This is not a short answer, therefore I asked you to come back, if you don't know. You have not even arrived at that stage, it seems, but indeed I'd cutomize and design the grid as you want it at runtime as the first step. Putting a grid on a form, the grid has no columns, no data, you have to define that in the first place. The leraning videos should help you get a grip on that control.

IV) How do I total picked orders
You got code for that

V) How do I set orders as paid
You got code for that at least from Mike.

So again, let's focus. Have you added error handling and what is output to the debug output window, if you did so. Do you have problems adding the error handling code itself? This is really the only thing right now, that will move you forward in having more info about the problematic parts of your and our code.

Bye, Olaf.

 

[foxprox558]

The Expression to find the orders is still not working, possibly because the field it should be checking to see if it's out is "O"-Out Of Store and "P"-Pending, So i'm not sure what would be true or false (.t. .f.) in any of those expressions.

 

[Olaf Doschke]

Well, you need to get terminology a little more straight, but having a C(1) field with "O" or "P" conditions would be field = "O" to find records with "O" in the field. .T. or .F. is what results from the condition/expression field="O", you have the same expressions in the WHERE clause as you knwo from IF statements. The WHERE clause of a query is nothing but an IF statement evaluated for every record. Do you know SQL at all?

Bye, Olaf.

 

[foxprox558]

No experience with SQL at ALL, I know it's a server-based database system, but as far as the inner workings of it, completely clueless.

 

[NasibKalsi]

foxprox558: thanks for telling after reviewing so many select statements that you are clueless.

anyway here is an other layman solution you might experiment and then enhance as you understand.

in the init of your form open or use two tables customer.dbf and details.dbf
details is indexed on CUSTID.

setup a relation on CUSTID.

have a field in the details as SELECTED, as pointed out before.

have also a field on the details table as PAID and AMOUNT

let the user selected the invoices by clicking(CheckBox) on the second grid (details)

during navigation in grid1, the grid2 (details) will automatically show all records for that customer.

at the point when user clicks paid, total the amount field based on PAID = .f. and SELECTED = .t.
add any taxes, etc.

set the details table as paid for the selected invoice.



on the form create two grids. grid1 is for customer table and grid2 is for details table. do not add or remove any column. Also create a Command button call it Paid.

in the init of the form insert the following code

(this is in no way a solution but a simple demostration of the solution.)

* Code starts here for init()
set safe off
close databases
use customer in 0 alias customer
use details in 0 alias details
select details
index on CUSTID to t_custid

select customer
set relation to CUSTID into details

with thisform.grid1
.columncount = 2 && Change it to how many columns you want
.deletemark = .f.
.readonly = .f.
.recordmark = .f.
.gridlines = 1
.gridlinecolor = rgb(235,245,235)
.gridlinewidth = 2
.recordsource = 'customer'
.scrollbars = 2 && Vertical

.column1.width = 50
.column1.controlsource = "customer.CUSTID"
.column1.Header1.caption = "Cust ID"
.column1.Header1.fontbold = .f.
.column1.Header1.fontunderline = .f.
.column1.readonly = .t.
.column1.fontsize = 10

.column2.width = 60
.column2.controlsource = "customer.CNAME"
.column2.Header1.caption = "CustName"
.column2.Header1.fontunderline = .f.
.column2.readonly = .t.
.column2.text1.fontsize = 9

endwith


with thisform.grid2
.columncount = 4
.deletemark = .f.
.readonly = .f.
.recordmark = .f.
.gridlines = 3
.gridlinecolor = rgb(235,245,235)
.gridlinewidth = 2
.recordsource = 'details'
.scrollbars = 2 && Vertical


.column1.width = 50
.Column1.readonly = .f.
.Column1.Header1.caption = " Select"
.column1.controlsource = "details.SELECTED"
.Column1.fontsize = 9
.column1.header1.BackColor = rgb(250,100,100)

.Column1.addobject("Check1","CheckBox")
.Column1.removeobject("Text1")
.Column1.Check1.caption = ' '
.Column1.currentcontrol = "CHECK1"
.Column1.readonly = .f.
.Column1.sparse = .f.
.Column1.readonly = .f.
.Column1.visible = .t.

.column2.width = 90
.coLUMn2.controlsource = "details.CUSTID"
.Column2.Header1.caption = " Cust ID"
.Column2.currentcontrol = "Text1"
.column2.header1.fontunderline = .t.
.Column2.sparse = .f.
.Column2.readonly = .t.
.Column2.fontsize = 9

.column3.width = 40
.column3.controlsource = "iif(details.PAID,'Yes','No')" && PAID is logical field
.Column3.Header1.caption = " Paid"
.column3.header1.fontunderline = .f.
.Column3.readonly = .t.

.column4.width = 50
.column4.controlsource = "details.AMOUNT" && Or any other fields
.Column4.Header1.caption = " Amount"
.column4.header1.fontunderline = .f.
.Column4.readonly = .t.

.column1.dynamicbackcolor = "iif(details.PAID,rgb(250,100,100),rgb(100,250,100))" && If PAID is true it will show in RED
endwith

* Code ends here for init() method


**************************************************************
* in your Paid button click() method

sum AMOUNT to TotAmount for ( SOLD = .f. ) and ( SELECTED = .t. ) and ( CUSTID = customer.CUSTID )

* if you are satisfied with the result amount then you can issue the following
replace all PAID with .t. for ( SOLD = .f. ) and ( SELECTED = .t. ) and ( CUSTID = customer.CUSTID )


* i hope it may help

* try it
* nasib

 

[foxprox558]

Which variables are you trying to get from the details.dbf file?
the main 2 databases I have (As previously mentioned) are orders.dbf and accounts.dbf
and the main form looks like this

http://www.bau5computers.webs.com/hitout.png/

when you push search it puts the customer number (accounts.custno) the last name (accounts.last) and the first name(accounts.first) in a combobox. I need it to search the orders.dbf database, which has a similar customer number field (orders.custno) for the one in the combobox and put all the orders that are marked with pending (orders.stat [can be either "P" for pending or "O" for out]) and put them in the grid.

 

[Olaf Doschke]

Hi foxpro558,

> I need it to search the orders.dbf database, which has a similar customer number field (orders.custno) for the one in the combobox
very good.

Take a look back at my first answer:

LOCAL lcCustno
lcCustno = thisform.combo1.Value && You may need to get the custno from the combo.list array instaed.

Select * From Orders where custno = m.lcCustno AND orders.stat = "P" INTO CURSOR curOrders NOFILTER

Select curOrdersGRD 
ZAP IN curOrdersGRD 
Append FROM DBF("curOrders")

The first two lines just weren't included in the code block, sorry. But yes, of course the query for orders needs to incorporate the selected custno, and that must be taken from the comobobox value or it's list array. Now I put together all things needed to query pending orders and put them into the grid cursor generated in the form load.

If that does not display, you haven't completed the grid design and taken the steps to bind it to the curOrdersGRD cursor. It's a bit more code needed, as you can also see from Naibs solution.

Bye, Olaf.

 

[foxprox558]

what is 'm.lcCustno'? where does the 'm' come from?
also, is curOrdersGRD supposed to be the on-screen grid?

 

[NasibKalsi]

foxprox558: you should be able to follow the code as i put it. you should be able to modify the field names to your databse(table) field names. After you understand the code, then you can easily understand what Olaf, and others are trying to help you.

i will copy paste it for you with modifications. create a new form and create two grids in it. paste the code in init() of the form and run it.

you do not need 'combo' and 'find'. it will find it all the time without you do anything for each CUSTNO. 'search' - you can add after you see what is happening. Then finally add 'pay'.

* Code starts here for init()
set safe off
close databases
use accounts in 0 alias customer 
use orders in 0 alias details
select details
index on CUSTNO to t_custno

select customer
set relation to CUSTNO into details

with thisform.grid1
.columncount = 2 && Change it to how many columns you want
.deletemark = .f.
.readonly = .f.
.recordmark = .f.
.gridlines = 1
.gridlinecolor = rgb(235,245,235)
.gridlinewidth = 2
.recordsource = 'customer'
.scrollbars = 2 && Vertical

.column1.width	 = 50
.column1.controlsource = "customer.CUSTNO"
.column1.Header1.caption = "Cust No"
.column1.Header1.fontbold = .f.
.column1.Header1.fontunderline = .f.
.column1.readonly = .t.
.column1.fontsize	 = 10

.column2.width	 = 60
.column2.controlsource = "customer.LAST" 
.column2.Header1.caption = "LastName"
.column2.Header1.fontunderline = .f.
.column2.readonly = .t.
.column2.text1.fontsize	 = 9

.column3.width	 = 60
.column3.controlsource = "customer.FIRST" 
.column3.Header1.caption = "FirstName"
.column3.Header1.fontunderline = .f.
.column3.readonly = .t.
.column3.text1.fontsize	 = 9



endwith


with thisform.grid2
.columncount = 4
.deletemark = .f.
.readonly = .f.
.recordmark = .f.
.gridlines = 3
.gridlinecolor = rgb(235,245,235)
.gridlinewidth = 2
.recordsource = 'details'
.scrollbars = 2 && Vertical


.column1.width	 = 50
.Column1.readonly = .f.
.Column1.Header1.caption = " Select"
.column1.controlsource = "details.SELECTED"
.Column1.fontsize	 = 9
.column1.header1.BackColor = rgb(250,100,100)

.Column1.addobject("Check1","CheckBox")
.Column1.removeobject("Text1")
.Column1.Check1.caption = ' '
.Column1.currentcontrol = "CHECK1"
.Column1.readonly = .f.
.Column1.sparse = .f.
.Column1.readonly = .f.
.Column1.visible = .t.

.column2.width	 = 90
.coLUMn2.controlsource = "details.CUSTNO" 
.Column2.Header1.caption = " Cust No"
.Column2.currentcontrol = "Text1"
.column2.header1.fontunderline = .t.
.Column2.sparse = .f.
.Column2.readonly = .t.
.Column2.fontsize	 = 9

.column3.width	 = 40
.column3.controlsource = "details.STAT"
.Column3.Header1.caption = " Status"
.column3.header1.fontunderline = .f.
.Column3.readonly = .t.


.column1.dynamicbackcolor = "iif(details.STAT="P",rgb(100,250,100),rgb(250,,100,100))" && If STAT is Out it will show in RED
endwith

* Code ends here for init() method

* DO NOT USE THE FOLLOWING CODE YET. FIRST TEST THE ABOVE CODE.
**************************************************************
* in your Pay button click() method
select details
sum AMOUNT to TotAmount for ( SOLD = .f. ) and ( SELECTED = .t. ) and ( details.CUSTNO = customer.CUSTNO )

* if you are satisfied with the result amount then you can issue the following
replace all PAID with .t. for ( SOLD = .f. ) and ( SELECTED = .t. ) and ( CUSTID = customer.CUSTID )
select customer

 

[foxprox558]

Where are you getting this data source called 'details' from?!

 

[NasibKalsi]

Hi Again:

details is the same as orders.dbf . You can give any name like

use orders in 0 alias foxpro558
or
use orders in 0 alias details

or

use orders in 0 alias babaji

then you can refer to it as foxpro558, details or babaji.

make sure you add the field to orders called SELECTED as in the following code

use orders exclusive
alter table orders add SELECTED l

* copy and paste the above 2 lines in command window
* after SELECTED it is letter 'L' not numeric '1'

try it.

 

[foxprox558]

I keep getting a VERY annoying error in the line where you try 'use accounts'

ERROR: FILE IS IN USE

I'm quite peeved at this

 

[NasibKalsi]

type in command window

close databases


or exist the foxpro and restart again.

 

[foxprox558]

I've found the reason, its the 'in 0' of 'use accounts in 0 alias customer'
I took 'in 0' and changed it to 'in 1' and Now I get the error: "alias accounts not found

 

[NasibKalsi]

ok

1. do not modify anycode. it is working and i have tested.

2. leave 'in 0'

3. quit foxrpo

4. do not run anyother of your foxpro form

4. type the following command
use accounts

if this does not work, your database is not in the path. You have to find out where exactly is your databases (tables).
then once you know then use with fully qualified name. for example if your databases are on c: drive under subdirectory 'drycleaning' then you have to modify the line to read as

use c:\drycleaning\accounts in 0 alias customer
use c:\drycleaning\orders in 0 alias details

but your original message saying 'file is in use' tells that you have opened the file somehow.

let me know.

 

[foxprox558]

The File is not open ANYWHERE, and I am in the correct path, all the other databases work.
here is the code for the Search button (Populates combobox with customer first, last & custno)

USE accounts
thisform.combo1.Clear
thisform.combo1.Enabled=.t.
thisform.command2.Enabled=.t.
*nCol=0
*nDx=0
*LOCATE FOR ALLTRIM(accounts.last)=ALLTRIM(thisform.text1.Value)
*nCol=nCol+1
*nDx=nDx+1
*DO WHILE .not. EOF() .And. (ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.last) .OR. ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.phone))
*  thisform.combo1.AddItem(TRANSFORM(accounts.custno)+": "+ALLTRIM(accounts.last)+", "+ALLTRIM(accounts.first),nDx,nCol)
*  skip  
*enddo 
* In both cases
SELECT * FROM accounts WHERE (ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.last) .OR. ALLTRIM(thisform.text1.Value)=ALLTRIM(accounts.phone)) INTO CURSOR curFilteredAccounts
nDx=0
nCol=0
* Then EITHER 1) add these records to the combo:
SELECT curFilteredAccounts
nDx=nDx+1
nCol=nCol+1
SCAN
   thisform.combo1.AddItem(TRANSFORM(curFilteredAccounts.custno)+": "+ALLTRIM(curFilteredAccounts.last)+", "+ALLTRIM(curFilteredAccounts.first),nDx,nCol)
ENDSCAN

LOCAL lcCustno
lcCustno = thisform.combo1.Value && You may need to get the custno from the combo.list array instaed.
USE
SELECT 0
CLOSE TABLES
CLOSE DATABASES
CLOSE INDEXES

 

[NasibKalsi]

Please foxpro558. do not run search yet. i want you to confirm that grids are working.

If you want to use search as well then:

Change my code where it says customer to accounts, and details to orders.


but first tell me if the code worked, for the grids. Leave your old codes as is as Olaf and others may want to help you with other of your questions.

CREATE A NEW FORM AND ADD THE CODE IN THE INIT() OF THE FORM. THERE SHOULD BE ONLY 2 GRIDS ON IT NOTHING ELSE.

try it.

 

[foxprox558]

If you would please look at the picture I've provided, I would like to keep the same general Idea of the code that I originally had, as well as the setup. One Grid which contains orders. the combo box that you choose the customer from. but I did kind of get your code working (after about 5 errors) and I liked the check box idea to Hit the orders out.

 

[NasibKalsi]

*
* You can put the following code in the search button click() event
*

select customer
thisform.command2.Enabled =.f.
LOCATE FOR ALLTRIM(customer.last)=ALLTRIM(thisform.text1.Value)
if eof()
wait windows "Nothing Found for Last Name:"+thisform.text1.value
endif