Production Schedule into Array?

[MoGrace]

The 2 tables below are shown after the fact. The first is the table where machines are assigned each day to an employee per shift. The 2nd is the production table where each employee's production is entered for each machine per shift. What I would like to do is get a list that looks like the 2nd table from the 1st table so I can create a lookup to the employee assigned to a particular machine when entering the production data so the user is limited to the employee schedule which was created first. Make sense? How do I go about getting such a list?

sched_map.jpg

[MoGrace]

Re: Production Schedule into Array?

Any ideas on how to do this 'reverse crosstab'? I just need a push since I am clueless!

[Stan Mathews]

Re: Production Schedule into Array?

I don't see where the data is obtained for the third record in prodsplt. I see the first two and the fourth but not the machno 05 for file 1366.

[Stan Mathews]

Re: Production Schedule into Array?

I don't see where the data is obtained for the third record in prodsplt. I see the first two and the fourth but not the machno 05 for file 1366. How many mach# fields are there?

Looks like you just need to process m_sched. Loop through, filtering first for date if necessary. Create a record in prodsplt for each mach# field with a value. Sort prodsplt on machno, shift, and fileno.

[MoGrace]

Re: Production Schedule into Array?

Good catch Stan - File 1366 should have Mach# 05 in his schedule. I am working backwards from data already entered into the prodsplt table to create the m_sched table.

My current scenario is to get a list from the m_sched table for a particular date for every machine assigned to an employee and write that to a temp table that can then provide the employee fileno when the prodsplit data is entered for each machine. IOW when the data is entered for Mach # 05, only 1366 should be the choice for the fileno. When I get this list working, I will add the shift # too so the relevant shift and machno only has one fileno to select.

I have tried to loop thru the m_sched table incrementing a var to map tbl.mach+"x" six times without much luck so far...

[Stan Mathews]

Re: Production Schedule into Array?

Assigning to mach#

for qx = 1 to 6
eval("mach"+alltrim(str(qx))) = 'something
next

or reading it

for qx = 1 to 6
'something = eval("mach"+alltrim(str(qx)))
next

Something like this, I think. Knocked together in wordpad, don't have Alpha available at the moment.

tbl_dest = table.open("prdsplt")
tbl = table.open("m_sched")
tbl.fetch_first()
while .not. tbl.fetch_eof()
for qx = 1 to 6
if alltrim(eval("tbl.mach"+alltrim(str(qx)))) > ""
tbl_dest.enter_begin()
tbl_dest.prodate = tbl.schedate
tbl_dest.machno = eval("tbl.mach"+alltrim(str(qx)))
tbl_dest.shift = tbl.shift
tbl_dest.fileno = tbl.file_no
tbl_dest.enter_end()
end if
next qx
tbl.fetch_next()
end while
tbl.close()
tbl_dest.close()

[MoGrace]

Re: Production Schedule into Array?

Thanks Stan,
I am not too sure which way to go with this - table or list, but this got me the list I wanted:

dim tbl as p
dim idx as p
dim vdate as d
dim i as n = 1
dim recs as n
dim list as c = ""
dim x as c
dim vfile as c
dim vmach as c
dim vshift as c
dim fltr as c
tbl = table.open("m_sched")
vdate = {11/03/2015}
fltr = "schedate = "+s_quote(var->vdate)
idx = tbl.query_create("NT",fltr,"file_no")
recs = idx.records_get()
if recs < 1 then
       goto CLOSEIT
end if
tbl.fetch_first()
on error goto CLOSEIT
while .not. tbl.fetch_eof()
	i=1	
	for i = 1 to 6	
		x = alltrim(str(i))
		vfile = tbl.file_no
		vshift = tbl.shift
		vmach = EVAL("tbl.mach"+x)
		if vmach <> "" then
			list = list+crlf()+cdate(vdate)+"|"+vmach+"|"+vshift+"|"+vfile+"|"
		end if		
	next i
	tbl.fetch_next()
end while
CLOSEIT:
tbl.close()
list = sortsubstr(list,crlf())
msgbox("List",list)

I will try it as a function with a parameter for the date that I can run when I create a new production template - in which case the table idea is probably better...

[Stan Mathews]

Re: Production Schedule into Array?

If you're going to use an array then your list is probably the preferable result. I just don't use arrays that often.

Isn't eval() a useful tool though?

[MoGrace]

Re: Production Schedule into Array?

I haven't tried it yet, but a field rule with a filtered table lookup on this temp table should work fine. The table will be zapped and rebuilt before going to the prodsplt table where the lookup will be used. And yes EVAL() is very handy once I learned how to use it!

Edit: The field rule lookup works great, however my script was skipping records - fixed it in the function below.

FUNCTION Sched_LU AS C (vdate as d = {})
'DESCRIPTION: Populates a temp table with m_sched to get fileno for machno & shift in production template
	dim tbl as p
	dim tmp as p
	dim idx as p
	dim i as n = 1
	dim recs as n
	dim list as c = ""
	dim x as c
	dim vfile as c
	dim vmach as c
	dim vshift as c
	dim fltr as c
	zap_it("tmpsched")
	xbasic_wait_for_idle()
	tbl = table.open("m_sched")
	tmp = table.open("tmpsched")
	if vdate = {} then
		vdate = date()
	end if
	fltr = "schedate = "+s_quote(var->vdate)
	idx = tbl.query_create("NT",fltr,"file_no")
	recs = idx.records_get()
	if recs < 1 then
		goto CLOSEIT	
	end if
	tbl.fetch_first()
	on error goto CLOSEIT
	while .not. tbl.fetch_eof()
		i=1	
		for i = 1 to 6	
			x = alltrim(str(i))
			vfile = tbl.file_no
			vshift = tbl.shift
			vmach = EVAL("tbl.mach"+x)
			if vmach <> "" then
				tmp.enter_begin()
					tmp.schedate = vdate
					tmp.machno = vmach
					tmp.shift = vshift
					tmp.file_no = vfile
				tmp.enter_end(.t.)			
			end if			
		next i
		tbl.fetch_next()
	end while
	CLOSEIT:
	tbl.close()
	tmp.close()
	response = msgbox("View Records","Would you like to see the table?",292)
	if response = 7 then
		goto ENDIT
	else	
		A5_OPEN_DEFAULT_BROWSE("tmpsched.dbf",".t.","machno+shift") 
	end if
	
	ENDIT: 
	Sched_LU=dtoc(vdate)
END FUNCTION