Custom Update Script

[mazatec]

Hi,

I am trying to update some fields in a table from another table using the following script:

table1=table.open("day_times")
table2=table.open("clocking")
flg = .F. 'flag to see if we want to save/discard the new record

IF convert_date(table2.Date_c) = table1.Entry_date THEN
	IF table2.Type="002" THEN
	    IF table2.Emp_no=table1.Emp_no THEN
			table1.enter_begin(.t.)
			table1.Exit_date=convert_date(table2.Date_c)
			flg = .T.
			table1.enter_end(flg) 'don't save if nothing was written
		END IF
	END IF
END IF

table1.close()
table2.close()

However it is not actually writing anything to the table. I am not sure if the script is correct or not. Can someone please have a look and give me some pointers please?

Regards,
Beau

[JohnZaleski]

Re: Custom Update Script

I don't believe there is a built-in convert_date function.there is however a built-in a5_convert_date_string function.If convert_date is a user defined function, it is imposssible for anyone to decifer your code without knowing how it works(what is date_c).I suggest using a debug(1) in your code to follow the execution line by line.

[aschone]

Re: Custom Update Script

Beau -

Your code is only checking the first record of table1 against the first record of table2 and then closing the tables. Is that what you intended for it to do?

-Andy

[mazatec]

Re: Custom Update Script

John,
The convert_date function is a custom function i have.
Andy,
No i mean to loop through all records but unsure how to.

Regards,

Beau

[aschone]

Re: Custom Update Script

You need to make use of the <tbl>.Fetch_Next() and test for the <tbl>Fetch_Eof() using a while loop. Both of the examples linked below have sample code that fetches thru a table.

You will need to decide if you are going to fetch thru table1 or table2. Once you have chosen which table you are fetching thru you will need to perform lookups of the other table to grab the related record.

<tbl>.Fetch_Next()
http://support.alphasoftware.com/alp...NEXT%28%29.htm

<tbl>.Fetch_Eof()
http://support.alphasoftware.com/alp..._EOF%28%29.HTM

[mazatec]

Re: Custom Update Script

Andrew, thanks for pinting me in the right direction. I have changed my script but i am still having some problems with it, i have 2 tables in my database, clockings and day_times. The script loops through clockings table looking for exit times from employees and matches them to the entry date/times in the day_times table, however some clockings are matching and others dont when they should. I have attached the database, if any one has some time could they please have a look for me?

regards,
beau

[Tom Henkel]

Re: Custom Update Script

Beau,
You need to have loop through one table, and then use the emp_no key field from the clocking table as a fetch_find() key into the day_times table.

something like this:

tbl1 = table.open("clocking")
tbl2 = table.open("day_times")
tbl2.index_prinmary_put("emp_no")  [COLOR="Navy"]'this index must be defined[/COLOR]
tbl1.fetch_first()
while .not.tbl1.fetch_eof()
     ans1 =tbl2.fetch_find(tbl1.emp_no) 
     if ans1 > 0
          [COLOR="Red"][B]'your update code here[/B][/COLOR]
       else
          [COLOR="red"][B]' no matching record
          ' whatever error checking code you need here[/B][/COLOR]
    end if
    tbl1.fetch_next()
end while
end

Hope it helps,

Tom

[mazatec]

Re: Custom Update Script

Thanks Tom,
I have changed the script to:

dim tbl1 as P
dim tbl2 as P
tbl1 = table.open("clocking")
tbl2 = table.open("day_times")
tbl2.index_primary_put("Emp_No")
tbl1.fetch_first()
while .not.tbl1.fetch_eof()
	if tbl1.Type = "002" then		
     ans1 =tbl2.fetch_find(tbl1.emp_no) 
     if ans1 > 0
     	if tbl2.Exit_date = null_value() then
          msgbox("Found Record with exit", tbl2.Emp_no + " " +tbl2.Entry_time_c,UI_ATTENTION_SYMBOL)
          tbl2.change_begin()
		  tbl2.Exit_source = tbl1.Source
	   	  tbl2.Exit_date = convert_date(tbl1.Date_c)
		  tbl2.Exit_time = convert_time(tbl1.Time_c)
		  tbl2.change_end(.t.)
		  tbl1.fetch_next()
		else
		  msgbox("Record Has Exit", "Record has exit add new record",UI_ATTENTION_SYMBOL)
		  tbl2.enter_begin(.t.)
		  tbl2.Exit_source = tbl1.Source
		  tbl2.Emp_no = tbl1.Emp_no
		  tbl2.Exit_date = convert_date(tbl1.Date_c)
		  tbl2.Exit_time = convert_time(tbl1.Time_c)
	  	  tbl2.enter_end(.t.)
          'your update code here
          tbl1.fetch_next()
        end if
       else
          msgbox("No Found Record","No Found Record For " + tbl1.Emp_no + " " + convert_date(tbl1.Date_c),UI_ATTENTION_SYMBOL)   
          tbl2.enter_begin(.t.)
		  tbl2.Exit_source = tbl1.Source
		  tbl2.Emp_no = tbl1.Emp_no
		  tbl2.Exit_date = convert_date(tbl1.Date_c)
		  tbl2.Exit_time = convert_time(tbl1.Time_c)
	  	  tbl2.enter_end(.t.)
          ' no matching record
          ' whatever error checking code you need here
          tbl1.fetch_next()
    end if
    else if tbl1.Type = "001" Then
    msgbox("No exit found.", "Entry, not exit",UI_ATTENTION_SYMBOL)
    tbl1.fetch_next()
    end if
end while
tbl2.close()
tbl1.close()
end

I now works as expected.

Regards,
Beau

[Mike Wilson]

Re: Custom Update Script

Don't see how you got it to work, because I'm seeing that in the example I downloaded, your convert_date function does not produce a valid date from the value in the date_c field from the clocking table.

dim old_date as C="100526"
yy=left(old_date,2)
mm=substr(old_date,3,2)
dd=substr(old_date,5,2)
? dd+"/"+mm+"/"+yy
= "26/05/10"

?ctod(dd+"/"+mm+"/"+yy)
= {  /  /    }

[Tom Henkel]

Re: Custom Update Script

mike,

try this...

?ctod(mm+chr(92)+dd+chr(92)+yy)
= {05/26/2010}

It may have to do with date format issues.
mm/dd/yy or dd/mm/yy
:)

Tom

[Mike Wilson]

Re: Custom Update Script

mike,
It may have to do with date format issues.
mm/dd/yy or dd/mm/yy

Exactly my point!

[Tom Henkel]

Re: Custom Update Script

What I'm saying is that Beau may have his date format settings set to read dd/mm/yy I do think, however that Alpha stores dates in standard yyyymmdd format no matter what is specified in the settings.

later...
the convert_date won't work for any day past the 12th. (at least not on my machine)

later, later...
I went to my system control panel and changed my date format and the date worked fine.

?convert_date("100625")
= {25/06/2010}

[mazatec]

Re: Custom Update Script

Hi Tom and Mike,

My system settings are set at dd/mm/yyyy. Is there any way to check how the system settings are set so i can have an if statement to make sure the convert_date function works with different date formats?

Regards,
Beau

[mazatec]

Re: Custom Update Script

Hi Everyone,

Sorry to be a pain in the backside with this script again, however i am having troubles with it again now that i have some real data for it. The script is matching some records however others it isn't even though there is matching data. The script is below:

'Date Created: 12-Jun-2010 09:12:44 PM
'Last Updated: 12-Jun-2010 10:38:33 PM
'Created By  : Beau Woods
'Updated By  : Beau Woods
dim tbl1 as P
dim tbl2 as P
tbl1 = table.open("clocking")
tbl2 = table.open("day_times")
query.filter = "Type='002'"
tbl1.query_create()
tbl2.index_primary_put("Emp_No")
tbl1.fetch_first()
WHILE .not.tbl1.fetch_eof()
		ans1 =tbl2.fetch_find(tbl1.emp_no)
		IF ans1 > 0
			IF tbl2.Entry_date = convert_date(tbl1.Date_c) THEN
				IF  tbl2.Exit_date = null_value() THEN
					msgbox("Found Record without exit", tbl2.Emp_no + " " +tbl2.Entry_time_c,UI_ATTENTION_SYMBOL)
					tbl2.change_begin()
					tbl2.Exit_source = tbl1.Source
					tbl2.Exit_date = convert_date(tbl1.Date_c)
					tbl2.Exit_time = convert_time(tbl1.Time_c)
					tbl2.change_end(.t.)
					tbl1.fetch_next()
				ELSE
					msgbox("Record Has Exit", "Record has exit add new record",UI_ATTENTION_SYMBOL)
					tbl2.enter_begin(.t.)
					tbl2.Exit_source = tbl1.Source
					tbl2.Emp_no = tbl1.Emp_no
					tbl2.Exit_date = convert_date(tbl1.Date_c)
					tbl2.Exit_time = convert_time(tbl1.Time_c)
					tbl2.enter_end(.t.)
					'your update code here
					tbl1.fetch_next()
				END IF
			ELSE
				msgbox("No Entry")
				tbl2.enter_begin(.t.)
					tbl2.Exit_source = tbl1.Source
					tbl2.Emp_no = tbl1.Emp_no
					tbl2.Exit_date = convert_date(tbl1.Date_c)
					tbl2.Exit_time = convert_time(tbl1.Time_c)
					tbl2.enter_end(.t.)
					tbl1.fetch_next()
			END IF
		ELSE
			msgbox("No Found Record","No Found Record For " + tbl1.Emp_no + " " + convert_date(tbl1.Date_c),UI_ATTENTION_SYMBOL)
			tbl2.enter_begin(.t.)
			tbl2.Exit_source = tbl1.Source
			tbl2.Emp_no = tbl1.Emp_no
			tbl2.Exit_date = convert_date(tbl1.Date_c)
			tbl2.Exit_time = convert_time(tbl1.Time_c)
			tbl2.enter_end(.t.)
			
			tbl1.fetch_next()
		END IF
	
END WHILE
tbl2.close()
tbl1.close()
end

Any help would be greatly appreciated.
Regards,
Beau

[Tim Kiebert]

Re: Custom Update Script

I think you need to add another loop to find the correct date for the employee id. As it is, for each record in the clocking table you are always 'finding' the first instance of the emp_no.

I have a reasonable idea what you are trying to do but I think there is a step or two missing.

i would write out the actual steps including if this or that then such and such in psuedo code first. Then write the code to follow that.