Append Multiple Records for One in Transaction Table

[gordon2908]

Re: Append Multiple Records for One in Transaction Table

Hi Stan,

Just to let you know that the second option worked perfectly and I used the logic to correct step 3 code and that also worked.

I think I am all done now so many thanks for all the help it was much appreciated.

Regards

Gordon

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

I see it now. If no matches are found the script never resets to the top of the dest table so there are no possible matches for further tbl table records.

One way around that is

while .not. dest.fetch_eof()
	if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. 'if location type matches and destination location is not closed
		dest.change_begin()
		dest.Part = tbl.Part
		dest.Date = tbl.Date
		dest.putaway_qty = tbl.qty
		dest.GRN_Line = tbl.GRN_Line
		dest.ref = tbl.ref
		dest.closed = .T. 'set this or destination location will be reused
		dest.change_end()
		tbl.change_begin()
		tbl.location = dest.location 'write chosen location back to putaway table
		tbl.change_end()
		dest.fetch_first() 'move to the next destination location to be ready for next putaway item
		exit while 'done with this putaway item, get out of locations loop
	else
		if dest.eof() 
			dest.fetch_first()
			exit while
		end if
		dest.fetch_next() 'not done with this item, move to the next location and run check
	end if
end while

Another would be

tbl = table.open("putaway_locations_temp")
dest = table.open("locations")
tbl.fetch_first()
tbl.batch_begin()
dest.batch_begin()
while .not. tbl.fetch_eof()
	while .not. dest.fetch_eof()
		if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. 'if location type matches and destination location is not closed
			dest.change_begin()
			dest.Part = tbl.Part
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			dest.closed = .T. 'set this or destination location will be reused
			dest.change_end()
			tbl.change_begin()
			tbl.location = dest.location 'write chosen location back to putaway table
			tbl.change_end()
			exit while 'done with this putaway item, get out of locations loop
		else
			dest.fetch_next() 'not done with this item, move to the next location and run check
		end if
	end while
	dest.fetch_first() 'move to the next destination location to be ready for next putaway item
	tbl.fetch_next()
end while
dest.batch_end()
tbl.batch_end()
tbl.close()

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

Not really.

The inner while loop looks at destination records (dest) comparing them to the source record (tbl).

while .not. dest.fetch_eof()
		if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. 'if location type matches and destination location is not closed
			dest.change_begin()
			dest.Part = tbl.Part
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			dest.closed = .T. 'set this or destination location will be reused
			dest.change_end()
			tbl.change_begin()
			tbl.location = dest.location 'write chosen location back to putaway table
			tbl.change_end()
			dest.fetch_first() 'move to the next destination location to be ready for next putaway item
			exit while 'done with this putaway item, get out of locations loop
		else
			dest.fetch_next() 'not done with this item, move to the next location and run check
		end if
	end while

So if no matching loc_types, not set to closed, are found the inner loop ends with end while. The next statement is

tbl.fetch_next()

which proceeds to the next source (tbl) record and starts over.

You can put in a new command

debug(1)

just after

dest.batch_begin()

The debugger will appear and you can F12 to step through the script and might catch something.

[gordon2908]

Re: Append Multiple Records for One in Transaction Table

Hi Stan,

I have tested both the original and partial codes and found that if either doesn't find a match to the filters in the code then they exit the loop at that record and do not process any subsequent records. Any ideas?

Regards

Gordon

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

This uses the same logic for step 3 that the other code uses. Moved the batch commands outside the while loop. Write the location back immediately after the destination record is changed.

tbl = table.open("putaway_locations_partial")
dest = table.open("locations")
tbl.fetch_first()
tbl.batch_begin()
dest.batch_begin()
while .not. tbl.fetch_eof()
	while .not. dest.fetch_eof()
		if tbl.part = dest.part .and. tbl.qty <[COLOR="#FF0000"]=[/COLOR] dest.qty_avail 'if part is equal and the qty will fit
			dest.change_begin()
			dest.Date = tbl.Date 'overwrite the previous field value?
			dest.putaway_qty = dest.putaway_qty + tbl.qty [COLOR="#FF0000"]'add the tbl.qty to current dest.putaway_qty (not overwrite)[/COLOR]			
                        dest.GRN_Line = tbl.GRN_Line 'overwrite the previous field value?
			dest.ref = tbl.ref 'overwrite the previous field value?
			dest.change_end()
			tbl.change_begin()
			tbl.location = dest.location 'write chosen location back to putaway table
			tbl.change_end()
			dest.fetch_first() 'move to the next destination location to be ready for next putaway item
			exit while 'done with this putaway item, get out of locations loop
		else
			dest.fetch_next() 'not done with this item, move to the next location and run check
		end if
	end while
	tbl.fetch_next()
end while
dest.batch_end()
tbl.batch_end()
tbl.close()
dest.close()

[gordon2908]

Re: Append Multiple Records for One in Transaction Table

Hi Stan,

Good news!

The code still wouldn't run so I tried the old code and had the same issue. In desperation I simply closed the database and reopened and hey presto everything worked. The new code executed in less than 3 minutes for the full 900 records including the action scripting bits so you have one very happy Englishman over here. I will test the issue re no matching loc_type tomorrow and let you know.

By the way I removed the dest.closed = .T. as there is a field rule which does this automatically. I need the field rule in place as it closes the location when inventory information is imported.

If you have a chance to look at point 3 regarding the top up issue I would also appreciate it.

Thanks and Regards

Gordon

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

I don't see where I changed anything that would keep the tables from updating. I do notice that your code and my alteration did not set the locations table closed field to .T.. If we don't do that then the location is still .F. when we go to the next putaway record and the script will use it again. I did move the batch commands outside of the while loops. They are only needed once. I also moved the three commands to store the putaway location in the locations table but that wouldn't prevent the updating.

Trying again with making the script close the location when it is used.

tbl = table.open("putaway_locations_temp")
dest = table.open("locations")
tbl.fetch_first()
tbl.batch_begin()
dest.batch_begin()
while .not. tbl.fetch_eof()
	while .not. dest.fetch_eof()
		if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. 'if location type matches and destination location is not closed
			dest.change_begin()
			dest.Part = tbl.Part
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			dest.closed = .T.[COLOR="#FF0000"] 'set this or destination location will be reused[/COLOR]
			dest.change_end()
			tbl.change_begin()
			tbl.location = dest.location 'write chosen location back to putaway table
			tbl.change_end()
			dest.fetch_first() 'move to the next destination location to be ready for next putaway item
			exit while 'done with this putaway item, get out of locations loop
		else
			dest.fetch_next() 'not done with this item, move to the next location and run check
		end if
	end while
	tbl.fetch_next()
end while
dest.batch_end()
tbl.batch_end()
tbl.close()
dest.close()

[gordon2908]

Re: Append Multiple Records for One in Transaction Table

Hi Stan,

I have tried the new code and it did not update either the locations or putaway_location_temp table. It ran lighting quick but did nothing and I guess must be exiting out of the loop before making any changes. I also tried changing dest.fetch_first() to dest.fetch_next() but it still did not work.

There will eventually be around 10-15 different location types and 20,000 locations (I am currently testing with 11,000 locations and 5 types) with your idea would we need a query for each location type on each table and then run a script to update the selected records?

I have created action scripting to empty and pack the temp table and then append only the previously unprocessed items from the master table. Once these are run I call your script and when complete I post back from the temp to the master table again using action script. These bits work very quickly so I am happy to leave them as they are.

Regards

Gordon

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

This should work better. I don't know why I put the write back section where I did. This will eliminate the issue of reusing a location if a matching location type isn't found.

tbl = table.open("putaway_locations_temp")
dest = table.open("locations")
tbl.fetch_first()
tbl.batch_begin()
dest.batch_begin()
while .not. tbl.fetch_eof()
	while .not. dest.fetch_eof()
		if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. 'if location type matches and destination location is not closed
			dest.change_begin()
			dest.Part = tbl.Part
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			dest.change_end()
			tbl.change_begin()
			tbl.location = dest.location 'write chosen location back to putaway table
			tbl.change_end()
			dest.fetch_first() 'move to the first destination location to be ready for next putaway item
			exit while 'done with this putaway item, get out of locations loop
		else
			dest.fetch_next() 'not done with this item, move to the next location and run check
		end if
	end while
	tbl.fetch_next()
end while
tbl.batch_end()
dest.batch_end()
tbl.close()
dest.close()

There are several possibilities for using indexing and queries that might speed things up. The use of the temporary table is a good idea. Trying to code "in theory" with no actual tables is difficult. I am wondering if adding a section to update the "master" putaway_locations table when the record in the temp table is used, then deleting the record in the temp table would help or hinder. It might also help to query the locations table for each possible location type possible, append that type only from the putaway locations to the temp table, then process with the script but eliminate the location check in

if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f.

I think that would also allow us to change

dest.fetch_first()

back to

dest.fetch_next()

since we would then know we hadn't skipped over any wrong type locations.

[gordon2908]

Re: Append Multiple Records for One in Transaction Table

Hi Stan,

I am still having some issues and wonder if you can help.

1. When I ran the code the first time on a typical receipt which contains 900 records it took about 5 minutes. After running a few more test it then took over 50 minutes. I have tried a few tweaks but the only one that improved the situation was adding a batch operation to tbl as well as dest (code below)

tbl = table.open("putaway_locations_temp")
dest = table.open("locations")
tbl.fetch_first()
tbl.batch_begin()
while .not. tbl.fetch_eof()
dest.batch_begin()
while .not. dest.fetch_eof()
if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. 'if location type matches and destination location is not closed
dest.change_begin()
dest.Part = tbl.Part
dest.Date = tbl.Date
dest.putaway_qty = tbl.qty
dest.GRN_Line = tbl.GRN_Line
dest.ref = tbl.ref
dest.change_end()
put_in_was = dest.location
dest.fetch_first() 'move to the next destination location to be ready for next putaway item
exit while 'done with this putaway item, get out of locations loop
else
dest.fetch_next() 'not done with this item, move to the next location and run check
end if
dest.batch_end()
end while
tbl.change_begin()
tbl.location = var->put_in_was 'write chosen location back to putaway table
tbl.change_end()
tbl.fetch_next()
tbl.batch_end()
end while
tbl.close()
dest.close()

Is there anything else that can be done to improve this? I wondered if adding an index/searching for records in dest table would help. You will note tbl is now putaway_locations_temp I am using this as a transitional table which gets zapped then only the records where location is blank are appended. This reduces the number of records we need to process as the putaway_locations table will get quite large over time.

2. Through my tests I have found that if there is no matching loc_type then the code stuffs the previous records location into tbl.location and does the same for all subsequent records even though they do have a matching loc_type.

3. I also need to create a script that identifies existing locations in the locations table for the part being processed where the quantity could be "topped up" i.e. the location can hold 100, we have 20 to put away and there are only 50 in the location. Below is the code I wrote using your code as a template but unfortunately it does not work. I am again creating a transitional table (putaway_locations_partial). It kept failing on the location write back so I took this out to try to get it to work.

Thanks for any help you can provide

Regards

Gordon



tbl = table.open("putaway_locations_partial")
dest = table.open("locations")
tbl.fetch_first()
tbl.batch_begin()
while .not. tbl.fetch_eof()
dest.batch_begin()
while .not. dest.fetch_eof()
if tbl.part = dest.part .and. tbl.qty < dest.qty_avail 'if part is equal and the qty will fit
dest.change_begin()
dest.Date = tbl.Date
dest.putaway_qty = tbl.qty
dest.GRN_Line = tbl.GRN_Line
dest.ref = tbl.ref
dest.change_end()
dest.fetch_first() 'move to the next destination location to be ready for next putaway item
exit while 'done with this putaway item, get out of locations loop
else
dest.fetch_next() 'not done with this item, move to the next location and run check
end if
dest.batch_end()
end while
tbl.fetch_next()
tbl.batch_end()
end while
tbl.close()
dest.close()

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

We can speed thing using .batch_begin() and .batch_end(). Also, since we are moving back to the first of the locations table we need to close the location after it is used for a putaway. We can also do the postback as the putaway is done.

tbl = table.open("putaway_locations")
dest = table.open("locations")
tbl.fetch_first()
while .not. tbl.fetch_eof()
	[COLOR="#FF8C00"]dest.batch_begin()[/COLOR]
	while .not. dest.fetch_eof()
		if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. 'if location type matches and destination location is not closed
			dest.change_begin()
			dest.Part = tbl.Part
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			[COLOR="#FF0000"]dest.closed = .T.[/COLOR]
			dest.change_end()
			put_in_was = dest.location
			dest.fetch_first() 'move to the next destination location to be ready for next putaway item
			exit while 'done with this putaway item, get out of locations loop
		else
			dest.fetch_next() 'not done with this item, move to the next location and run check
		end if
		[COLOR="#FF8C00"]dest.batch_end()[/COLOR]
	end while
	[COLOR="#006400"]tbl.change_begin()
	tbl.location = var->put_in_was 'write chosen location back to putaway table
	tbl.change_end()[/COLOR]
	tbl.fetch_next()
end while
tbl.close()
dest.close()

[gordon2908]

Re: Append Multiple Records for One in Transaction Table

Hi Stan,

It did need to be dest.fetch_first as you thought as with the next command it stalled when the tbl.loc_type_1 changed. I tried with the change to first and it worked but did take a couple of minutes to run. This is not an issue as they will only do this once per day.

You are also right there is nothing to indicate the process has run but I had realised this and there will be a post back from "locations" to "locations_putaway" to populate the location chosen. This will be on the ref field which is a unique identifier for the records in "location_putaway" and has been populated in "locations" by this code. I will then exclude records where tbl.location<>"" to ensure the process can only run once for each record.

Thanks for all the help but don't be surprised if I get stuck again! I have been using Alpha5 for 20 years since v4 but always used action scripting until now but will definitely take time to learn more about code as I can see the power!

By the way I live in England 30 miles east of London and hence the odd times of my posts for you.

Regards

Gordon

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

Ok, your original code attempt makes more sense now having seen the table structures. It stopped on the first record because the second record has closed marked T which ended the

while .not. tbl.fetch_eof() .and. tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f.

I added a debug() statement to this version. When the code is run you can use the F12 key to step through the execution watching what happens. If it gets stuck somewhere you can Halt Script.

debug(1)
tbl = table.open("putaway_locations")
dest = table.open("locations")
tbl.fetch_first()
while .not. tbl.fetch_eof()
	while .not. dest.fetch_eof()
		if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f. [COLOR="#0000FF"]'if location type matches and destination location is not closed[/COLOR]
			dest.change_begin()
			dest.Part = tbl.Part
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			dest.change_end()
			dest.fetch_next() [COLOR="#0000FF"]'move to the next destination location to be ready for next putaway item[/COLOR]
			exit while [COLOR="#0000FF"]'done with this putaway item, get out of locations loop[/COLOR]
		else
			dest.fetch_next() [COLOR="#0000FF"]'not done with this item, move to the next location and run check[/COLOR]
		end if
	end while
	tbl.fetch_next()
end while
tbl.close()
dest.close()

The line
dest.fetch_next() 'move to the next destination location to be ready for next putaway item
should probably be
dest.fetch_first() 'move to the start of destination locations to be ready for next putaway item
because we shouldn't assume the locations are in order and we might have passed over a non closed location that wasn't the right type.

I did notice that there is no check to see if there is an available location of any type. No change is made to the putaway_locations table to indicate that the item has been putaway so running the process again will putaway the same items.

[gordon2908]

Re: Append Multiple Records for One in Transaction Table

Hi Stan,
Unfortunately that hasn't worked as the first record from "putaway_locations" has been posted to every filtered line of "locations" and then it seemed to get stuck in a loop and I had to end task to stop it.

I do need to match up the two part fields as what I am trying to achieve is a warehouse product putaway system where empty locations are selected and the parts assigned to these locations. Hopefully screen shots of the tables will be attached.

Regards

Gordon


Screen Shot 2018-09-13 at 08.25.38.png

Screen Shot 2018-09-13 at 08.27.06.png

[Stan Mathews]

Re: Append Multiple Records for One in Transaction Table

That's a bit hard to work out having no sample data but I think it would be something like.

tbl = table.open("putaway_locations")
dest = table.open("locations")
dest.fetch_first()
tbl.fetch_first()
while .not. tbl.fetch_eof()
	while .not. dest.fetch_eof()
		if tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f.
			dest.change_begin()
			dest.Part = tbl.Part
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			dest.change_End()
		end if
		dest.fetch_next()
	end while
	dest.fetch_first()
	tbl.fetch_next()
end while
tbl.close()
dest.close()

But I would think you want to match up tbl.part to dest.part?

tbl = table.open("putaway_locations")
dest = table.open("locations")
dest.fetch_first()
tbl.fetch_first()
while .not. tbl.fetch_eof()
	while .not. dest.fetch_eof()
		if tbl.Part = dest.Part .and. tbl.loc_type_1 = dest.loc_type .and. dest.closed = .f.
			dest.change_begin()
			dest.Date = tbl.Date
			dest.putaway_qty = tbl.qty
			dest.GRN_Line = tbl.GRN_Line
			dest.ref = tbl.ref
			dest.change_End()
		end if
		dest.fetch_next()
	end while
	dest.fetch_first()
	tbl.fetch_next()
end while
tbl.close()
dest.close()