Managing a Transaction Table

[sollasollew]

Re: Managing a Transaction Table

Many, many thanks. I will print this out and follow to the letter.

Eric

[Mike Wilson]

Re: Managing a Transaction Table

field names are in blue and tablenames are in red and the first field value for the parent table needed to start the xdialog is in pink. There were some other notes and commented out things that were from a different application that were confusing that I took out also.

FUNCTION update_status AS C ( )

' This function adds an update to a status

dim vclid as C
vclid = parentform:browse2:[COLOR="Blue"]client_id[/COLOR].value

'Create an XDialog dialog box to prompt for parameters.

DIM vLCats as C
dim shared vCat as C=""
DIM SHARED vStat as C=""

vLCats= table.external_record_content_get("[COLOR="Red"]Status[/COLOR]","[COLOR="blue"]Category[/COLOR]","","")
vLCats = word_unique(vLCats,crlf())

' GENERATE THE GLOBAL ARRAY FOR EACH CATEGORY
		dim vLCDIM as C
		dim vLCVAR as C
		dim vFilter as C
		dim vConv as C
		dim cycle as N = 0
		dim vLCatW as C
		dim shared vReply as C

	for each foo in vLCats
		vLCatW = foo.value		
		vConv = alltrim(vLCatW)
		'MSGBOX("CONV", vConv)
		
		query.filter = "[COLOR="blue"]Category[/COLOR] = "+s_quote(vConv)
		vfilter = convert_expression(query.filter,"V")
		'MSGBOX("FILTER", vConv+crlf()+ vfilter)
		
		vLCVar = vConv+"Items"
		vLCDIM = "dim "+alltrim(vLCVar)+" as C"
		Evaluate_template(vLCDIM)
		eval(vLCVar) = table.external_record_content_get("[COLOR="Red"]Status[/COLOR]","[COLOR="blue"]Status[/COLOR]","",vFilter)
	next

'======== generate the xDialogs ===================
RETRY:
	Gchoices = vLCats
	vCat = word( Gchoices, 1, crlf() ) 
	Itemchoices = [COLOR="magenta"]Housing[/COLOR]items
	
	dlg_text = <<%dlg%
	{position=1.5,2.5}
	{background=#175,165,135}
	{xmargin=2,2}
	{region}
	{font=Arial,9,b}
	{frame=1,0:Category}
	{font=Arial,9,n}
	[.30,5vCat^#Gchoices!newd_*];
	{endregion}
	{region}{comment this region is just to space things out a bit}
	{comment this adds even more space - maybe too much}
	{endregion}
	{region}
	{font=Arial,9,b}{frame=1,1:Status }{font=Arial,9,n}
	[.30,15vstat^#Itemchoices];
	{endregion}
	{condition=.t.}
	{lf};
	<10OK> <10Cancel>
	%dlg%
	
	dim vresult as C 
	vresult=ui_dlg_box("Item Choices", dlg_text, <<%code%
	IF left( a_dlg_button, 5 ) = "newd_"
	 IF a_dlg_button = "newd_change"
	 	
	 		Itemchoices = eval(alltrim(vCat)+"items")
	 
	 END IF
	 a_dlg_button = ""
	END IF
	%code%)
	

	if vResult = "Cancel"
		end

	else if vCat = "" then
		p_msg_box("ALERT","The Category was not chosen correctly.  Try again.","<15Yes> <15Cancel>","")
			select
		    case vReply = "Yes"
		    	GOTO RETRY
		    case vReply = "Cancel"
		    	vProceed = "no"
		    	end 
		end select
		
	else if vStat = "" then
		p_msg_box("ALERT","The Status was not chosen correctly.  Try again? .","<15Yes> <15Cancel>","")
		select
		    case vReply = "Yes"
		    	GOTO RETRY
		    case vReply = "Cancel"
		    	vProceed = "no"
		    	end 
		end select
	end if
	
	'msgbox("","Category = "+ vCat + crlf() + "Status =  " + vStat)

	dim t as P
	t = table.current(2)   '("transactions")
	t.enter_begin()
	t.[COLOR="Blue"]client_id [/COLOR]= vclid
	t.[COLOR="blue"]date[/COLOR] = date()
	t.[COLOR="blue"]category[/COLOR] = vcat
	t.[COLOR="blue"]status[/COLOR] = vstat
	t.enter_end(.t.)
	't.close()

'Pause the script for a instant until the user interface has finished repainting.
xbasic_wait_for_idle()

'Refresh data in current form at parent level.
'Can only resynch data in View mode, so save record first to be sure that layout is in View mode.
	topparent.Commit()
	topparent.Resynch()
	topparent.Refresh_Layout()
	'Refresh  'Browse1' in current form.
	topparent:Browse1.repaint()
	
END FUNCTION

[sollasollew]

Re: Managing a Transaction Table

You're extremely generous.

I figure the function has to know where to get the Status and the Status Category and what to do with them once the user makes his/her choice. I'm not good enough at reading the code so I don't really know where to look (ie what functions require what parameters).

Eric

[Mike Wilson]

Re: Managing a Transaction Table

What values are you referring to and I can highliht them for you.

[sollasollew]

Re: Managing a Transaction Table

Thanks. Is there any bright way to find the values required to run the function other than going through the code line by line?

EG

[Mike Wilson]

Re: Managing a Transaction Table

Gotcha, more or less. The question with this function is to figure out what tables and field names it needs and where to place them. BTW, what is @BROWSE48976FA2? I click on it and get an error message.

Eric

I haven't the foggiest. Where are you seeing that?

LATER: OK, I see it. It is nothing I know of. Extraneous, I'd delete it.

[sollasollew]

Re: Managing a Transaction Table

Gotcha, more or less. The question with this function is to figure out what tables and field names it needs and where to place them. BTW, what is @BROWSE48976FA2? I click on it and get an error message.

Eric

[Mike Wilson]

Re: Managing a Transaction Table

Eric,
Sorry I didn't get back to you sooner, I was away for the weekend.

The update_status() is a user defined function (UDF) that I wrote, not by action scripting, although I use the scripting genie often within script writing. This code is xbasic and xdialog. What you see come up is not a Form but is an xdialog that is generated within the code of the update_status() function. I "borrowed" this xdialog script from someone who posted an example on the message board a little over a year ago and have used it for just this purpose in several applications. I should, by all rights reference who that is, but I didn't register it. I like it very much, although I can't take credit for it. It uses a very mainstream A5 function - table.external_record_content_get(). This retrieves a filtered or unfiltered values of one field from a table in a list format. The script then uses each value in this primary list as an element in 2 ways. One is as an element in a name for a variable that is dynamically generated for each value in the primary list (using the very powerful eval() and evaluate_template() functions), and secondly as a variable to generate a second list that by it use links it to the value of the primary list, within the filter component of the table.external_record_content_get() function.

Both the primary and seconday lists get displayed in the xdialog. As the primary list item is chosen, it calls the variable associated with it to display in the secondary list. I will have to be honest with you that I know what it does and have to this point gained some insight into its functionality, but I haven't the complete insight or knowledge to completely reproduce it from scratch. I have to give thanks to someone who posted it, very thankful for it, and I modify it as needed with my growing learning of xdialog, learning each time I use and change it for my next use. I am however neither the author nor do I have the skill to be the author of that xdialog. But I thought it would fit well for your needs, so I threw it out there for you to see it work as a solution to what you were describing your needs to be.

OK, enough said at this point. I know what I have writen might be hyrogliphics for you, as parts of this code is for me, but I threw this out for you to realize your needs are achievable. Let me know if there is anything more I can answer.

[sollasollew]

Re: Managing a Transaction Table

Great quote in your sig file.

Thanks so much for building this example for me. It does exactly what I want. Couple of questions :

-Is there a way for the user to manually refresh the form?
-More vexing... I see you have an update_status() function as the onPush action of the add button. Where does the form that appears come from and how does it know what table to update? Did you code that or was the code written by a genie or something?

Thanks much.

Eric

[sollasollew]

Re: Managing a Transaction Table

The problem with the cascading is that it will only fill the status and not the status type field. This is potentially workable hopwever. I will explore some more.
Eric

[Mike Wilson]

Re: Managing a Transaction Table

Eric,
Wonder if this might help as a solution. Althought this example has the classic Browse refresh issue (I really wish this could get resolved!!!).

[sollasollew]

Re: Managing a Transaction Table

You can set a field as a cascading lookup - its like a tree. See image.
In field rules, at the records onsaverecord event, you could write the new value to a separate transaction table that might look like or similar to this:

clientid, newvalue, transdate, transtime, changedby, computername

The table need not be part of the current set for the current form, but could be viewed from a button bringing up a different form, filtered on that client.

This would likely require some xbasic.

Ouch! This is al,most what we'd like to do. There would be one more field however- statusItem. StatusItem would hold the 'subject' of the status, say housing status or mental status:

001 housingstatus homeless 5-1-08
001 mentalhealth psychotic 5-15-08
001 housingstatus group home 6-15-08
002 mentalhealth depress 5-5-08

With this table I can get the surrent status and see the status changes over time. The issue is that 'homeless' is only relevant for housingstatus whereas psychotic is only relevant for mental health. The form needs to feed the right choices to a drop down, depending upon what status is selected in the prior drop down. Unfortunately I'm away on business right now and can't try Keith's idea on this machine.

Eric

[martinwcole]

Re: Managing a Transaction Table

You can set a field as a cascading lookup - its like a tree. See image.
In field rules, at the records onsaverecord event, you could write the new value to a separate transaction table that might look like or similar to this:

clientid, newvalue, transdate, transtime, changedby, computername

The table need not be part of the current set for the current form, but could be viewed from a button bringing up a different form, filtered on that client.

This would likely require some xbasic.

[Tom Cone Jr]

Re: Managing a Transaction Table

Eric, Alpha Five includes rudimentary change log or audit trail capabilities. Check the help file topic "Using the Audit Trail Feature". A more complete approach is described in detail in Dr. Wayne's book "Xbasic for Everyone".

[Mike Wilson]

Re: Managing a Transaction Table

Eric,
In line with what Tom wrote, it is not clear to me what your goal is ultimately. With what you wrote, it actually sounds like there would only be one status field that holds values for many different status types so that each record would have data relative to only the single status condition... say viral load.... and have nothing regarding housing condition. But what if two or more status items changed? Does that now constitute two or more records entered for that client?

Or are you trying to have one record for each client with the many fields holding current data (viral load, housing status, employment, etc ) and the process is one of updating the fields of a single client record, and not having historical values as separate records? Or duplicating the entire record with changes in the field values for the changes in statuses so there is a historical record of all the changes?

The approach to these scenerios is vastly different.