>>Hi everybody,
>>
>>I'd like to show distribution of accounts based on queue names or based on clients or based on product line or based on status. The distribution is going to be by department. Right now all the accounts are for Outpatient department ("O"). When I'm using distribution by Queues, I start from queues and left join to Trans file. Queues table has departments in it. In other cases I decided to show only actual distribution.
>>
>>So, this is my original code
>>

>>insert into curStats (cFullName, I, H, M, O, nTotal) ;
>>		select cFullName, I, H, M, O, cast(I+H+M+O as n(5)) as nTotal from curStatistics

>>
>>But in cases other than Queues I may have less fields. What would be the best approach for me to overcome the problem? Build the insert command on the fly?
>>
>>Thanks in advance.
>
>I think with APPEND BLANK, followed by REPLACE, it is easier to conditionally set, or not set, a specific field.


This works quite nicely:

lparameters tnOption
local loMessage, loWait, lcNotify, lcInsertCmd
local lnI, lnH, lnM, lnO, lnTotal
store 0 to lnI, lnH, lnM, lnO, lnTotal

lcNotify = set('notify')
set notify on
set talk on
set talk window

with thisform

	if not .lSilentMode
		loMessage = createobject("cWaitMessage", "Getting Data...")
		loWait = createobject("cMessage", "Getting Data...")
		.WaitMode(.t.)
	endif

	do case
	case m.tnOption = 1 && by Queue
		GetQueuesDistribution()
	case m.tnOption = 2 && by Clients
		GetClientsDistribution()
	case m.tnOption = 3 && by Product Line
		GetProductLineDistribution()
	case m.tnOption = 4 && by Status
		GetStatusDistribution()
	endcase

	if m.lcNotify = 'OFF'
		set notify off
	endif
	set talk off

	.oXTab.RunXTab()

	zap in curStats

	text TO lcInsertCmd TEXTMERGE NOSHOW PRETEXT 7
		insert into curStats (cFullName, I, H, M, O, nTotal)
			select cFullName,
			<<IIF(TYPE('CurStatistics.I')='N','I','00000')>> as I,
			<<IIF(TYPE('CurStatistics.H')='N','H','00000')>> as H,
			<<IIF(TYPE('CurStatistics.M')='N','M','00000')>> as M,
			<<IIF(TYPE('CurStatistics.O')='N','O','00000')>> as O,
			 cast(<<IIF(TYPE('CurStatistics.I')='N','I+','') + IIF(TYPE('CurStatistics.H')='N','H+','') + IIF(TYPE('CurStatistics.M')='N','M+','') + IIF(TYPE('CurStatistics.O')='N','O','0')>>
			 as n(5)) as nTotal from curStatistics
	ENDTEXT

	lcInsertCmd = chrtran(m.lcInsertCmd, chr(13) + chr(10)," ")
	&lcInsertCmd

	select curStats

	sum I, H, M, O, nTotal to lnI, lnH, lnM, lnO, lnTotal

	.txtHTotal.value = m.lnH
	.txtITotal.value = m.lnI
	.txtMTotal.value = m.lnM
	.txtOTotal.value = m.lnO
	.txtTotal.value = m.lnTotal

	go top in curStats

	.WaitMode(.f.)
	.refresh()
endwith