Results 1 to 19 of 19

Thread: Append Multiple Records for One in Transaction Table

  1. #1
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default Append Multiple Records for One in Transaction Table

    I need to append multiple records for each record in my transactional table and to make matters more difficult the number of records required varies but is indicated in a field within the table. Is this possible?

    So far I have not built any applications using code but recognise this will probably be needed for this task so any help with the coding would be much appreciated.

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Append Multiple Records for One in Transaction Table

    Seems like a similar question was asked recently but I can't find it. The number of dest.fieldname = tbl.fieldname between the enter_begin() and enter_end() in the code below would vary depending on how many fields you have to "append".

    Code:
    tbl = table.open("atest")'original table
    dest = table.open("atest_dest")'table records are being entered/appended to
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
    	how_many = tbl.qty 'qty fld holds number of records to be created
    	for qx = 1 to how_many
    	dest.enter_begin()
    	dest.id = tbl.id
    	dest.name = tbl.name
    	dest.enter_end()
    	next
    	tbl.fetch_next()	
    end while
    tbl.close()
    dest.close()
    Note: The similar question I was thinking of involved creating an output text file with a number of lines for each record based on a number in a field in the record. Not quite the same but the logic was pretty much the same.
    Last edited by Stan Mathews; 09-10-2018 at 02:57 PM.
    There can be only one.

  3. #3
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default Re: Append Multiple Records for One in Transaction Table

    Hi Stan,

    Tried it this morning and it works a treat!

    Many thanks for the help.

    Gordon







    Quote Originally Posted by Stan Mathews View Post
    Seems like a similar question was asked recently but I can't find it. The number of dest.fieldname = tbl.fieldname between the enter_begin() and enter_end() in the code below would vary depending on how many fields you have to "append".

    Code:
    tbl = table.open("atest")'original table
    dest = table.open("atest_dest")'table records are being entered/appended to
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
    	how_many = tbl.qty 'qty fld holds number of records to be created
    	for qx = 1 to how_many
    	dest.enter_begin()
    	dest.id = tbl.id
    	dest.name = tbl.name
    	dest.enter_end()
    	next
    	tbl.fetch_next()	
    end while
    tbl.close()
    dest.close()
    Note: The similar question I was thinking of involved creating an output text file with a number of lines for each record based on a number in a field in the record. Not quite the same but the logic was pretty much the same.

  4. #4
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default Re: Append Multiple Records for One in Transaction Table

    Hi Stan,

    I wonder if you can help again?

    I now need to update a new table (locations) with data from each record in the table created with the code you supplied (putaway_locations). I have tried to write the code (below) using similar logic to that you provided but when run it only updates from the first record and does not read/update from any of the subsequent ones. Any help would be greatly appreciated.

    Regards

    Gordon




    tbl = table.open("putaway_locations")
    dest = table.open("locations")
    tbl.fetch_first()
    while .not. tbl.fetch_eof() .and. 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()
    tbl.fetch_next()
    end while
    tbl.close()
    dest.close()

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.

    Code:
    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?
    Code:
    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()
    There can be only one.

  6. #6
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default 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

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.


    Code:
    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. '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()
    			dest.fetch_next() '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
    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.
    Last edited by Stan Mathews; 09-13-2018 at 09:34 AM.
    There can be only one.

  8. #8
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default 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

  9. #9
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.

    Code:
    tbl = table.open("putaway_locations")
    dest = table.open("locations")
    tbl.fetch_first()
    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.closed = .T.
    			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()
    end while
    tbl.close()
    dest.close()
    There can be only one.

  10. #10
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default 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()

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.

    Code:
    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.
    There can be only one.

  12. #12
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default 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

  13. #13
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.

    Code:
    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()
    			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()
    There can be only one.

  14. #14
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default 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

  15. #15
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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.

    Code:
    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 <= 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 'add the tbl.qty to current dest.putaway_qty (not overwrite)			
                            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()
    There can be only one.

  16. #16
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default 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

  17. #17
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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).

    Code:
    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.
    There can be only one.

  18. #18
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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

    Code:
    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

    Code:
    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()
    There can be only one.

  19. #19
    Member
    Real Name
    Gordon Shand
    Join Date
    Apr 2017
    Posts
    15

    Default 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

Similar Threads

  1. Add records to a transaction table in xBasic
    By Michael1954 in forum Mobile & Browser Applications
    Replies: 0
    Last Post: 01-25-2018, 11:41 AM
  2. Inserting records using a transaction for table with child rows
    By jmsilva in forum Application Server Version 11 - Web/Browser Applications
    Replies: 5
    Last Post: 05-19-2012, 10:03 PM
  3. Append Multiple Records
    By Zico2399 in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 01-15-2011, 06:23 AM
  4. Append transaction records based on external table lookup
    By confused in forum Alpha Five Version 7
    Replies: 11
    Last Post: 02-28-2007, 02:34 PM
  5. Can update transaction table field during append
    By Greg Fong in forum Alpha Five Version 4
    Replies: 4
    Last Post: 06-05-2001, 05:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •