Why is "While" not completing properly

[Tanman]

I have been practising writing some code.

This is the code I have written...

'Date Created: 06-Aug-2011 11:04:27 PM
'Last Updated: 07-Aug-2011 01:17:42 AM
'Created By  : weiyentan
'Updated By  : weiyentan
'This script goes thru all the contacts and enters the contact id to the transaction table.
dim contacttbl as p
dim transtbl as p

contacttbl =  table.open("contact")
transtbl = table.open("transactions")


contacttbl.fetch_first()

WHILE .not. contacttbl.fetch_eof()
	SELECT
		CASE contacttbl.type = "Client"
			transtbl.enter_begin()
			transtbl.contactid = "contacttbl.contactid_field.value"
			transtbl.description = "Subscription Fee"
			transtbl.flddebit = 50
			transtbl.enter_end(.T.)
		CASE contacttbl.type = "Supplier"
			
			transtbl.enter_begin()
			transtbl.contactid = "contacttbl.contactid_field.value"
			transtbl.fldcredit = 100
			transtbl.description = "Invoice"
			transtbl.enter_end(.T.)						
			
		END Case

END SELECT
contacttbl.fetch_next()
END WHILE

transtbl.close()
contacttbl.close()

What I want it to do is go through the contact table records from top to bottom and then enter values into a transaction table for each contact. It checks to see a type of contact .. whether its a supplier or a client.

If its a client then it writes subscription in the description field and 50 in the flddebit field
If its a supplier then it writes invoice in the description field and 100 in the flddebit field.

What I have noticed is that when it makes its evaluation. . .it will write the first record fine.

If the type of the contact next record is different....then it stops.

ie contact 1 = supplier the script will run onto the next record (completing the action in the transaction table)
contact 2 = supplier the script will add that to the transaction table..
contact 3 = client....the script will not add this record in at all and the script stops.

I have attached the database for those that are interested to have a look. I would be most grateful.....and also..in the field that reads contactid in the code...how do I get that to have the contactid at the record the script is at...at the time?

Your help is most appreciated.
And thanks to Tom Cone Jr who has spent the time with me .... I have just started to grasp how the structure and flow of Xbasic works and eager to get my hands dirty.


training database.zip

[Stan Mathews]

Re: Why is "While" not completing properly

...and also..in the field that reads contactid in the code...how do I get that to have the contactid at the record the script is at...at the time?

transtbl.contactid = "contacttbl.contactid_field.value"

should be

transtbl.contactid = contacttbl.contactid_field.value

the first writes the text "contacttbl.contactid_field.value" to the transtbl.contactid

the second writes the contents of contacttbl.contactid_field.value to the transtbl.contactid

I'll take a look at the sample.

[Stan Mathews]

Re: Why is "While" not completing properly

Your attempt was not a bad effort.

There is no End Case command in Alpha so that line was being interpreted as a simple End.

If you remove that line your script will run normally. Note the use of contacttbl.contactid_field.value is also incorrect. The contactid field in the contact table is simply named contactid. After obtaining the contacttbl pointer you reference the field with contacttbl.contactid, not contacttbl.contactid_field.value
I have attached a text file with a corrected script if you want to examine it after proceeding with your practice.


training database - 08-06-2011 -1.txt

[Tanman]

Re: Why is "While" not completing properly

Thanks Stan!

I went ahead and made those changes and it now works. I also added a line in there

transtbl.date = date()

so it will enter the current date.

I think I have one other validating rule to put in there...that is the code will check if the entry has not already been added with a record in the same year

ie once the record has been added with 7/8/2011 date stamp, the rule will check if an existing record with the date stamp exists or not, and if it those goes to the end.

I think an if then else method would be the right one to use but I will make a good go of it first before I write back again.

Thanks once again.

[Stan Mathews]

Re: Why is "While" not completing properly

I'm not clear what you mean by "goes to the end".

You say...

ie once the record has been added with 7/8/2011 date stamp, the rule will check if an existing record with the date stamp exists or not, and if it those goes to the end.

Once the record with 7/8/2001 date stamp is entered/added then yes, a record with the date stamp exists. I think what you want to do is check to see if a record with a particular date stamp exists, then decide whether or not to add a record. There's a function here that will help you with that if you are have trouble with that aspect.

[Tanman]

Re: Why is "While" not completing properly

Hi Stan,

Wasn't so bad as I thought.

Have wrote the amendments for the current year.

'Date Created: 06-Aug-2011 11:04:27 PM
'Last Updated: 07-Aug-2011 10:30:19 AM
'Created By  : weiyentan
'Updated By  : weiyentan
'This script goes thru all the contacts and enters the contact id to the transaction table.
dim contacttbl as p
dim transtbl as p

contacttbl =  table.open("contact")
[B]transtbl = table.open("transactions")

transtbl.fetch_first()
while .not. transtbl.fetch_eof()
	
	if year(transtbl.date) = year(now())then 
		ui_msg_box("Rollover","The Rollover has been done, Please add transactions manually. ", UI_STOP_SYMBOL)
end 
	else
	transtbl.fetch_next()
	end if
end while[/B]

contacttbl.fetch_first()

WHILE .not. contacttbl.fetch_eof()
	
	SELECT
		CASE contacttbl.type = "Client"
			transtbl.enter_begin()
			transtbl.date =date()
			transtbl.contactid = contacttbl.contactid
			transtbl.description = "Subscription Fee"
			transtbl.flddebit = 50
			transtbl.enter_end(.T.)
		CASE contacttbl.type = "Supplier"			
			transtbl.enter_begin()
			transtbl.date =date()
			transtbl.contactid = contacttbl.contactid
			transtbl.fldcredit = 100
			transtbl.description = "Invoice"
			transtbl.enter_end(.T.)						
			
	

END SELECT
contacttbl.fetch_next()
END WHILE
transtbl.batch_end()
transtbl.close()
contacttbl.close()

It works! Had to revise a few things but it worked great in the end. Glad to put it somewhere else if it will help anybody.

[Stan Mathews]

Re: Why is "While" not completing properly

Functional but possibly inefficient. If you create an index on the transactions date field, call it transyd, expression year(date) you can check the existence of the current year with one line. No need to fetch through records in the transactions table at all.

'Date Created: 06-Aug-2011 11:04:27 PM
'Last Updated: 07-Aug-2011 10:30:19 AM
'Created By  : weiyentan
'Updated By  : weiyentan
'This script goes thru all the contacts and enters the contact id to the transaction table.
dim contacttbl as p
dim transtbl as p

contacttbl =  table.open("contact")
transtbl = table.open("transactions")
[COLOR="#FF0000"]if exist(year(now()),"transactions","Transyd")[/COLOR]
     ui_msg_box("Rollover","The Rollover has been done, Please add transactions manually. ", UI_STOP_SYMBOL)
     end 
end if 
contacttbl.fetch_first()
transtbl.batch_begin()
WHILE .not. contacttbl.fetch_eof()
	
	SELECT
		CASE contacttbl.type = "Client"
			transtbl.enter_begin()
			transtbl.date =date()
			transtbl.contactid = contacttbl.contactid
			transtbl.description = "Subscription Fee"
			transtbl.flddebit = 50
			transtbl.enter_end(.T.)
		CASE contacttbl.type = "Supplier"			
			transtbl.enter_begin()
			transtbl.date =date()
			transtbl.contactid = contacttbl.contactid
			transtbl.fldcredit = 100
			transtbl.description = "Invoice"
			transtbl.enter_end(.T.)						
			
	

END SELECT
contacttbl.fetch_next()
END WHILE
transtbl.batch_end()
transtbl.close()
contacttbl.close()

[Tanman]

Re: Why is "While" not completing properly

Thanks Stan,

I am going to try this now and see how it works. I am getting the hang of this..its making more and more sense.