E9.2 How to check imported grid from excel are unique

[JohnDanter2]

Hi folks

I am writing an APPL to help me do a mass proce upload and I have a F5741061 that stores what they've entered on screen and then a UBE processes them.

Part of the use case is to make sure the imported grid records are unique on Item/Branch/Supplier/Catalogue/Quantity Break.
If the grid records were on the DB them it's easy, but any ideas on how to do it when they are still just on the grid?

I'm having a long day and can't think how best to do this efficiently

Thanks

John

 

[BOster]

You could write them to jdeCache and throw an error when jdeCacheAdd throws an error for a duplicate key. If you are only checking for dups your jdeCache struct would only need to have PK members.

 

[JohnDanter2]

Yeah I've written them to the actual file and can check this way. It works on on upload of the grid but has caught me out on editting a column, as I then need to update the 2 lines.
So if my grid says
1) A B C
2) A B D
it's fine, but if someone changeds a GC to
1) A B C
2) A B C
I should then throw an error, but I've already written A B D to the DB so it's hard to check as I can't then update the record, as A B C (D) is part of the records key

Quite tricky this

Maybe if I add a unique constraint on the table on these keys, that will do the trick too

 

[JohnDanter2]

OK sorted

Each time I run Grid Record is Exited Inline I get a UKID for the table (X0022) and save this in the GC (hidden and only obtained if 0) and save this record to the DB

Then each time someone either loads or edits a grid row I delete the record using the hidden X0022 value and reinsert with the new GC values and then do my select state,ment to chek the uniqueness.

Messy but is working like a charm. I then disabled add and update in the headerless details add record and update record events and delete if users click Cancel

Thanks Brian, but writing a caching on a Friday before my xmas holidays is beyond me today lol

Have a good xmas

Row Exit & Changed - Inline // Sequence Grid Line No

  // Sequence Grid Line No
 -If GC EDI Line # [EDLN] is less than or equal to "0"
  |    VA frm_LineNumber_LNID [LNID] = [VA frm_LineNumber_LNID [LNID]]+1
  |    GC EDI Line # [EDLN] = VA frm_LineNumber_LNID [LNID]
  |    //
  |    // Get UKID for each Grid record - store in EDBT
  |   -GetNextUniqueKeyID(X00022.GetNextUniqueKeyID)
  |         "F5741061" -> szObjectName [OBNM]
  |         VA frm_NextNumber_N001 [N001] <- mnUniqueKeyID [UKID]
  |    GC EDI Batch # [EDBT] = [VA frm_NextNumber_N001 [N001]]
  End If
   //
 -F5741061.Delete  [Index 8: EDI]
       GC EDI Batch # [EDBT] = TK EDI - Batch Number [EDBT]
 -F5741061.Insert  [Index 8: EDI]
       GC EDI Co [EKCO] -> TK Company - Key (EDI - Document Key Co) [EKCO]
       GC EDI Doc # [EDOC] -> TK EDI - Document Number [EDOC]
       GC EDI DCT [EDCT] -> TK EDI - Document Type [EDCT]
       GC EDI Line # [EDLN] -> TK EDI - Line Number [EDLN]
       GC Tran Set [EDST] -> TK EDI - Transaction Set Number [EDST]
       GC Translation Format [EDFT] -> TK EDI - Translation Format [EDFT]
       GC Tran Date [EDDT] -> TK EDI - Transmission Date [EDDT]
       GC Processed [EDSP] -> TK EDI - Successfully Processed [EDSP]
       GC EDI Batch # [EDBT] -> TK EDI - Batch Number [EDBT]
       GC Branch [MCU] -> TK Business Unit [MCU]
       GC Supplier Number [AN8] -> TK Address Number [AN8]
       GC Item Number [ITM] -> TK Item Number - Short [ITM]
       GC 2nd Item Number [LITM] -> TK 2nd Item Number [LITM]
       GC 3rd Item Number [AITM] -> TK 3rd Item Number [AITM]
       GC Catalog  [CATN] -> TK Catalog [CATN]
       GC Currency Code [CRCD] -> TK Currency Code - From [CRCD]
       GC UoM [UOM] -> TK Unit of Measure as Input [UOM]
       GC Price [PRRC] -> TK Amount - Unit Cost [PRRC]
       GC Quantity Break [UORG] -> TK Units - Order/Transaction Quantity [UORG]
       GC Effective Date [EFTJ] -> TK Date - Effective [EFTJ]
       GC Effective Date [EFTJ] -> TK Date - Expired [EXDJ]
       GC User ID [USER] -> TK User ID [USER]
       GC Program ID [PID] -> TK Program ID [PID]
       GC Work Stn ID [JOBN] -> TK Work Station ID [JOBN]
       GC Date Updated [UPMJ] -> TK Date - Updated [UPMJ]
       GC Time of Day [TDAY] -> TK Time of Day [TDAY]
       GC Effective Date. [VC10A] -> TK Video Constant A - length 10 [VC10A]
 -If SV File_IO_Status is not equal to CO SUCCESS
  |    Set Grid Cell Error(FC Grid, <Currently Selected Row>, GC QuantityBreak, "Y570001")
  |    VA frm_cRecordErrorY_EV01 [EV01] = "Y"
  End If
  //
 -F5741061.Open
     
 -F5741061.Select  [Index 9: EDI plus Cat]
       GC EDI Co [EKCO] = TK Company - Key (EDI - Document Key Co) [EKCO]
       GC EDI Doc # [EDOC] = TK EDI - Document Number [EDOC]
       GC EDI DCT [EDCT] = TK EDI - Document Type [EDCT]
       GC EDI Line # [EDLN] <> TK EDI - Line Number [EDLN]
       GC Branch [MCU] = TK Business Unit [MCU]
       GC Supplier Number [AN8] = TK Address Number [AN8]
       GC Item Number [ITM] = TK Item Number - Short [ITM]
       GC Catalog  [CATN] = TK Catalog [CATN]
       GC Currency Code [CRCD] = TK Currency Code - From [CRCD]
       GC Quantity Break [UORG] = TK Units - Order/Transaction Quantity [UORG]
 -F5741061.FetchNext  [Index 9: EDI plus Cat]
       VA frm_Dummy_USER [USER] <- TK User ID [USER]
 -If SV File_IO_Status is equal to CO SUCCESS
  |    Set Grid Cell Error(FC Grid, <Currently Selected Row>, GC QuantityBreak, "Y570001")
  |    VA frm_cRecordErrorY_EV01 [EV01] = "Y"
  End If
 -F5741061.Close

 

[DaveWagoner]

Each time I run Grid Record is Exited Inline I get a UKID for the table (X0022) and save this in the GC (hidden and only obtained if 0) and save this record to the DB

Then each time someone either loads or edits a grid row I delete the record using the hidden X0022 value and reinsert with the new GC values and then do my select state,ment to chek the uniqueness.

i love stuff like this. works fine for now and you might find something better in the future given time and space but it does what you need. thanks for the share

 

[JohnDanter2]

Yeah it does the job and is handy to save for future use, the combiation you are searching for can be anything but the principle will be the same

Give all records a batch #
Make the record unique with a single column key when entering the row for the 1st time ever
delete on that key
insert all GCs again, be they new or changed
then check your combination in that batch