Have created new table with following fields:orno, pono, seqn etc. New session to maintain table will not accept same orno, pono with different seqn on db.insert. Index consists of all 3 fields with no duplicates. Any ideas on what to check?

Thanks.

Is this the primary index? What exactly is the error? The only thing I can think of offhand is that the data dictionary might think that there is a index where orno and pono can not have duplicate values. So I would check the table and make that the session actually does a create new data dictionary for the table. Also remember to log off and back on to get the new DD.

Mark

Thanks Mark,
This is a new table created to track actual ship dates per sales order line. I need the seqn to record partial shipments per order line - similar to multi receipts on a purchase order line. The orno, pono, seqn is the primary/only index on this table. As part of the insert there is some data validation against tdsls041 for orno, pono, item must exist and dqua cannot exceed oqua. I was able to insert multi records but then data validation didn't work, knowing my users I need to make this idiot proof so the data feed going to corporate HQ contains valid data. How would you tackle this? DD seems to be fine, I have deleted and re-created table just in case it wasn't quite right.

Everything you mentioned sounds okay. I have done similar things to track the generation of in bound and outbound, so the concept sounds okay.

Are there any useful errors in the log file that may point to a specific field? Maybe you could post the code - that may help someone to find the problem.


Mark

Mark,
I decided to start over from scratch. In debug everything looks ok, has the correct values and as soon as I try to 'save' the message 'record already exists' is displayed. Something is very weird so I thought best thing to delete session etc and try again. I'll let you know how it goes....
Thanks for the help.

You can check in Display Table Maintenance (ttaad4500) to see what the Runtime thinks the index is. It sure shounds like the runtime thinks it's Orno/Pono.

In Display Table Maintenance, when you first pull up the table, the primary index will be underlined. Enter '*' at the choice field to see the index order.

Then, hit the space bar at the choice field to get a drop menu down listing of options. Go to the 'Browse' menu and choose 'Change Key'. How many keys are there? As you choose them, you will see the new keys underlined in the records...anything unexpected there? Is sequence # part of all of the indexes as seen at Runtime?

If still in doubt, try inserting a record through General Table Maintenance (ttaad4100). If it's an index error you will get it here too.

Thank you lbencic,
Only 1 index and DD does see it correctly. I think is was something else that I messed up, tried so many variations and probably totally confused myself and the system.

Tried inserting a record via GTM?

Help!
Ok I started over and still get the ttstps0114 - Record already exists...that is if I try to enter another record through the session.
Scenario:
orno - 404024
pono - 50
seqn - 1
if I try to enter 404024, 50 seqn 2 through the session I get the error message.
If I do the same thing through GTM it works!
What am I missing?

Can you post the code? or is this through the standard program code only?

Also, post the options for your session - is your table the main table for the session?

Hi lbencic,
New table Actual Ship Dates is main table of maintain session.
Only field section has any code...

field.tccom807.pono:
before.zoom:
tdsls041.orno = tccom807.orno
when.field.changes:
select max(tccom807.seqn):hold.seqn
from tccom807
where tccom807._index1 = {:tccom807.orno, :tccom807.pono}
selectdo
hold.seqn = hold.seqn + 1
selectempty
hold.seqn = 1
endselect
tccom807.seqn = hold.seqn

zoom.from.tdsls4510s000:
on.entry:
import("tdsls041.pono", tccom807.pono)
import("tdsls041.item", tccom807.item)
import("tdsls041.oqua", tccom807.dqua)
display(tccom807.item)
display(tccom807.dqua)

field.tccom807.dqua:
when.field.changes:
select tdsls041.orno, tdsls041.pono, tdsls041.item, tdsls041.oqua
from tdsls041
where tdsls041._index1 = {:tccom807.orno, :tccom807.pono}
and tdsls041.item = :tccom807.item
selectdo
select tccom807.*
from tccom807
where tccom807._index1 = {:tdsls041.orno, :tdsls041.pono}
order by tccom807._index1
selectdo
dqua = dqua + tccom807.dqua
endselect
if (dqua + tccom807.dqua) > tdsls041.oqua then
set.input.error("tccomg100.1")
dqua = 0
endif
endselect

Do I need to code the db.update/insert? I thought standard should take care of this.

Tina,

I have found that it is a bad idea to perform additional select statements on your main table without using aliases. My guess would be that the select in the section field.tccom807.dqua is what is getting you.

If you were to change that select so that you use a table alias, I think things migth improve. Try:

select com807.dqua:hold.dqua
from tccom807 com807
where com807._index1 = {:tdsls041.orno, :tdsls041.pono}
order by com807._index1
selectdo
dqua = dqua + hold.dqua
endselect

In any event, in order to find out where things are going wrong, have you tried watching the variable tccom807.seqn in the debugger? Use the command "T tccom807.seqn" and the debugger will tell you when the value changes.

Hope this helps.

Cheers,

-Dennis

Thank you Dennis,
The seqn is incrementing the way it should, no problem there.
I tried your suggestion regarding usage of an alias for checking summing up the previous dqua already recorded in the main table, unfortunately this didn't resolve the 'record already exists' error message.

Anything else I might try?

Just noticed that you are using the phrase 'record already exists' and while that sounds similar to 'duplicate record' error (I think) 100, it would appear to not be the same thing. If you are getting a ttstp message and not the ugly white error message box, then I would think it is not an index thing.

Do you have a commit.transaction() command or a db.update/db.insert commands in your code? I am assuming that your session is of Type 1/2/3 and in such a session, the above commands would confuse BAAN.

Cheers,

-Dennis

Have you tried adding the main.table.io event and the before.write section. In this section you can check your tccom807 record. It looks like before you do the insert the seqn variable is getting reset back to 1. In the before.write section you could do your find for the sequence.


main.table.io:
before.write:
select max(tccom807.seqn):hold.seqn
from tccom807
where tccom807._index1 = {:tccom807.orno, tccom807.pono}
selectdo
hold.seqn = hold.seqn + 1
selectempty
hold.seqn = 1
endselect
tccom807.seqn = hold.seqn


Mark

Not sure without debugging. Looks like the sequence number will update, but only if you change the position. Are you doing that?

Debugging should resolve - try setting a dummy message, I think the main table io / before write section would be good:


main.table.io:
before.write:
message("This is the after write section")


But a break point on the message, and when it stops, check all the values. Trace the value of your seqn. I think you saw already that it is updating ok initially, but maybe it gets overwritten.

Also, similar to this, you can set tracing options on your session to see what values it is trying to insert into the table. This thread has a discussion and attached instructions on session tracing:

http://www.baanboard.com/baanboard/showthread.php?s=&threadid=7456&highlight=TRACE

Edit: I just saw Mark's post - his update of the sequence number is a more standard approach, and should work.

Hi Dennis,
I don't think it is the table def'n since I can enter thru GTM.

Do you have a commit.transaction() command or a db.update/db.insert commands in your code? I am assuming that your session is of Type 1/2/3 and in such a session, the above commands would confuse BAAN.

Yes the session is type 1/2/3 and No the only code written is the field section posted - plus declarations - no other code whatsoever has been written.

Hi Mark,
I'm not too familiar with main.table.io, am I understanding this correctly?....move the seqn 'stuff' from the when.field.changes of com807.pono to the new main.table.io section, before.write. Is there anything else that would have to change?

Thanks lbencic, will work thru suggests, never used session trace before, could be interesting....

At this point I think if you create the main.table.io sections and watch what happens there you may see where the tccom807.seqn gets reset. I am hoping that by moving the section I mentioned then writing the record would start working. Once the records are going into the table then we can make sure they get the correct values. So if this fixes adding the record then the tccom807.dqua may also need to be moved.

Mark

wild stab at this - make sure the allowable indices by session are set correctly. Its the "Change Key" option on form 1 of the maintain sessions.

Thanks Nick,
You can learn something new everyday, didn't know that even existed.

did that fix the problem ??

Nick,
In this case there is only 1 index on the table, just working on the main.table. io suggestion....

If the re-coding to the main.table.io does not fix the problem:


Sometimes when you generate sessions bugs like this happen,as apposed to creating the session/forms etc on your own.

I would suggest (only if you originally generated this session and its forms etc...) that you delete the session and create a new one and attach the script/forms to it.

Ok...main.table.io... the 'record already exists' error shows up before it even gets to the before.write...and seqn = 0.

Mark you are partially correct, the seqn is being reset to the last seqn value in the 807 table for the specific orno/pono combo overwriting the value set in the field section....

what if I move the seqn increment code to after the comparison of dqua and oqua....???

if the main table is tccom807 and you are selecting the tccom087 table via sql statments in the script - you may be loosing the pointer as maintained by the main table (standard program). use the function on.main.table to call youre code that selects the tccom087 table.

example :

Suppose you have a table to which you can add numbers only
| if a certain value is present in the main table

declaration:
long go_ahead

field.pctst999.number:
check.input:
on.main.table( check_number, 5 )
if not go_ahead then
set.input.error("pctst0003", 5) |"Number %d not present"
endif

functions:
function void check_number( long number )
{
select pctst999.*
from pctst999
where pctst999.number = :number
as set with 1 rows
selectdo
go_ahead = TRUE
selectempty
go_ahead = FALSE
endselect
return

what if I move the seqn increment code to after the comparison of dqua and oqua....???

That worked! No more 'record already exists'! I'm it's not the cleanest code ever written but it works.

Thank you to all for your tips and guidance, greatly appreciated!

Nick, thank you for your suggestion of using the on.main.table function, but I didn't see your post until after I tried moving the code around.

I think Nick and dnnslbrwn are correct about where you are losing your pointers to tccom807. I think what I would do is this:


field.tccom807.seqn:
before.input:
| Inserting a new record so first get the new sequence number.
| Using an alias - we do not want to confuse the main table pointers
select max(com807.seqn):hold.seqn
from tccom807 com807
where com807._index1 = {:tccom807.orno, :tccom807.pono}
selectdo
hold.seqn = hold.seqn + 1
selectempty
hold.seqn = 1
endselect
tccom807.seqn = hold.seqn
display(tccom807.seqn)

field.tccom807.dqua:
when.field.changes:
| Now check the input(tccom807) delivered qty. I assumed there is
|only 1 tdsls041 row
select tdsls041.orno, tdsls041.pono, tdsls041.item, tdsls041.oqua
from tdsls041
where tdsls041._index1 = {:tccom807.orno, :tccom807.pono}
and tdsls041.item = :tccom807.item
as set with 1 rows
selectdo
endselect
| Sum the current tccom807 dqua for the current Order/Position
select sum(com807.dqua):hold.dqua
from tccom807 com807
where com807._index1 = {:tdsls041.orno, :tdsls041.pono}
order by com807._index1
selectdo
endselect
| Verify that the new input quantity
if (dqua + tccom807.dqua) > tdsls041.oqua then
set.input.error("tccomg100.1")
dqua = 0
| Make them input a valid number
input.again()
endif



It just dawned on me as I was writing this message. In your first posted code you used the tccom807.pono field to determine your next seqn field - but you used the when.field.changes event. This event will not trigger if the pono stays the same. In my example I used before.input of tccom807.seqn and you could probably use the on.input of tccom807.pono. Just remember that when the user inserts a record you always want this code to execute, otherwise the seqn will not get changed.


Mark

Thank you Mark,
I was trying to use combo orno + pono to determine if there was already a 'partial' shipment and if so then the seqn would have to be incremented. I think it's better if the seqn is display on the form and increment the value as needed behind the scenes - KISS principle is required here! Your idea of using on.input of tccom807.pono should do the trick for this. Don't know why I didn't think of using sum() for dqua - must have been a blonde moment. (yes, I am a blonde, naturally).

Everyone has those moments - so lets not just limit it to us blondes(male or female). :)

Mark

EUREKA!
Perseverance and a little help from some 'friends' does pay-off.
Thank you to all for your help and suggestions, the final version of what works is attached.

Ooops - I didn't do that right - please see next post for script...

Just one thougth... when you ftp your source from UNIX to Windows, make sure that your ftp program is set to ASCII in order to add the extra carriage returns or line feeds (which ever UNIX does not use - I forget).

To make it readable, I had to upload it to my UNIX server in binary mode and drop it back down in ASCII mode.

Really glad you got it to work!

Cheers,

-Dennis

Thanks Dennis,
I printed the script to ASCIF without a file extension, and then found out that I couldn't attach it without an extension. Thought I'd be real smart and just renamed the file with .txt - apparently not a good move. I'll do this again and post new version of script. Thanks for letting me know!

Ok...here is the script...readable I hope....