JimbobGilb
Posts: 28
|
| Posted: 11/17/2011, 1:23 AM |
|
Hi There,
The code below works fine but only validates against the 1st record in the PendingPicking table where the TransactionGroup are the same, but this could relate to numerous lines for picking and I want to see if a partnumber exists in any of the records where the TransactionGroup match.
If CCDLookup("PartNumber", "PendingPicking", "TransactionGroup="& DBConnection1.ToSQL(PickedItems.TransactionGroup.value,ccsFloat), DBConnection1) <> PickedItems.PartNumber.value Then
PickedItems.Errors.addError("Part number not shipped.")
end if
If anyone has any suggestions as to what I have missed that would be great.
Thanks
|
 |
 |
andrewi
Posts: 162
|
| Posted: 11/17/2011, 1:56 PM |
|
Which event have you put this code in? Have you put it in BeforeShowRow? (assuming this is a grid or editable grid component)
Can you describe the contents of table PendingPicking. Would you expect it to return only one PartNumber per TransactionGroup? If you might have a set of PArtNumbers in a specified TransactionGroup then remember that CCDLookup will only return one of those numbers.
I don't fully understand the intention of the table, but something like the following might be more plausible (very rough code - assuming PartNumber is numeric):
If CCDLookup("PartNumber", "PendingPicking", "TransactionGroup="& DBConnection1.ToSQL(PickedItems.TransactionGroup.value,ccsFloat)
& " AND PartNumber = " & PickedItems.PartNumber.value, DBConnection1) <> NULL Then
...
i.e. you are searching PendingPicking table for both the TransactionGroup AND the PartNumber in question.
|
|
|
|
JimbobGilb
Posts: 28
|
| Posted: 11/17/2011, 11:59 PM |
|
HI,
Thanks for getting back to me.
I am inserting this to an onvalidate event. The Idea is that the PendingPicking table is populated when the office staff make a shipment which can have numerous different PartNumbers on one TransactionGroup (or Despatch Note Number). Then the PickedItems table is populated when Despatch department scans the items he has picked into the PickedItems record form and then the onvalidate checks what he has picked against the PendingPicking table against the TransactionGroup and if it is an incorrect part number for example it throws up the Error "Incorrect Item Picked"
PartNumber is a Text incase this needs changing.
Hopefully this is possible unless I am expecting to much from the OnValidate event.
I look forward to hearing your thoughts,
|
|
|
|
andrewi
Posts: 162
|
| Posted: 11/18/2011, 2:00 PM |
|
Thanks for explaining.
Your basic problem is that "CCDLookup("PartNumber", "PendingPicking", "TransactionGroup="...) " will only return one record whenever it is called. So, for example, if your TransactionGroup is No 100, this will always return the first part number in group 100, regardless of how many partnumbers are in that group. So you're only ever going to check against this one part number, which I think is what you say you're experiencing.
So you need to put more conditions in to narrow it down: If there is not a record in the PendingPicking table with THIS part number AND THIS transaction groupID, then raise the error. (I put this the wrong way round in my suggestion above).
If CCDLookup("PartNumber", "PendingPicking", "TransactionGroup="& DBConnection1.ToSQL(PickedItems.TransactionGroup.value,ccsFloat)
& " AND PartNumber = " & DBConnection1.ToSQL(PickedItems.PartNumber.value, DBConnection1, ccsText), DBConnection1) = "" Then ... (add error)
Again I may not be helping by offering untried code. It may be that CCDLookup will return a null if the part number/TransactionGroup combination doesn't exist, in which case the conditional expression would have to be "...) IS NULL Then..." or maybe "...) = NULL Then... ".
This sounds quite appropriate for the OnValidate event. Unless you are using an editable grid, in which case ues OnValidateRow event.
|
|
|
|
JimbobGilb
Posts: 28
|
| Posted: 11/24/2011, 1:29 PM |
|
Hi Andrew,
Thanks for the suggestionsabove, i really appreciate it, but I am struggling to get this working for some reason. Each block works on its own if I break it down but for some reason when I add the second clause the & "AND ......... doesn't work.
Any thoughts would be appreciated.
Many Thanks,
James
|
|
|
|
TheunisP
Posts: 342
|
| Posted: 11/24/2011, 2:32 PM |
|
isnt the idea to use a COUNT(*) - then you can just check if it returns a 0 - meaning it is not found anything else means that the combo already exists?
|
|
|
|
andrewi
Posts: 162
|
| Posted: 11/25/2011, 8:49 AM |
|
TheunisP has the right answer: use
If CCDLookup("Count(*)", "PendingPicking", "TransactionGroup="& DBConnection1.ToSQL(PickedItems.TransactionGroup.value,ccsFloat)
& " AND PartNumber = " & DBConnection1.ToSQL(PickedItems.PartNumber.value, DBConnection1, ccsText), DBConnection1) <> 0 Then
So, if the count is anything other than zero, raise the error.
If you're still having trouble, can you try executing the SQL directly against the database - SELECT Count(*) FROM PendingPicking WHERE .... and build up the where clause here using test values to produce an expected result.
|
|
|
|
|
