Hidden Rooms & Secret Stairways:

Hidden Rooms & Secret Stairways: Custom Data in a Vanilla QAD Database

Introduction • Who is this Guy? Greg Sevison • Employer? AmSafe • IT Background Applications Development ………… 26 Years • History w/ QAD and Progress? Progress Programming ……………. 6 Years QAD Application ……………………. 5 Years Progress DBA ………………………. 4 Years

Why This Session? • Session for the “Newbie” • The Challenge Develop a custom QAD application that requires the storage of custom data. • The Restrictions 1. CHUI (.Net not being utilized) 2. No 3rd Party Solutions 3. No Consultants 4. No Money

What is the User Work Table? The User Work Table (usrw_wkfl) is a database table, defined and established by QAD, for the purpose of providing a storage area for users to store data for custom applications. The Table resides in the standard QAD database schema, requiring no changes or setup to the existing database. It is FREE.

The Table Field Name Format Description ---------------------- ---------------------- --------------------------------------------------- usrw_domain x(8) Domain usrw_key1 x(8) Index Field usrw_key2 x(8) Index Field usrw_key3 x(8) Index Field usrw_key4 x(8) Index Field usrw_key5 x(8) Index Field usrw_key6 x(8) Index Field usrw_charfld[15] x(8) Character Fields – 15 available usrw_datefld[4] 99/99/99 Date Fields – 4 available usrw_decfld[15] ->>,>>9.99 Decimal Fields – 15 available usrw_intfld[15] ->,>>>,>>9 Integer Fields – 15 available usrw_logfld[15] yes/no Log Fields – 15 available usrw_user1 x(8) User Field #1 usrw_user2 x(8) User Field #2

The Indexes Index Name Unique Field(s) Ascending ---------------------- ---------- ---------------------------------------------- --------------- usrw_index1 Yes usrw_domain Yes usrw_key1 usrw_key2 usrw_index2 No usrw_domain Yes usrw_key3 usrw_key4 usrw_index3 No usrw_domain Yes usrw_key5 usrw_key6

What are the Benefits? • No Need to change Schema • No Need to recompile existing code • Does not effect Patches and Upgrades • Provides Numerous Field Types and Indexes • It’s FREE

What about the “Gotcha”s? • Limited Number of Data Fields • Only Three Indexes Available • Field Names are Generic

AmSafe’s Challenge Utilizing the CHUI interface, vanilla QAD, and the standard User Work Table provided by QAD, develop a custom application to setup, maintain, and generate custom product and shipping labels, using Loftware as it’s labeling software.

Table (AmSafe Example) Label Master (xxlbmst) usrw_domain “CPUSD” usrw_key1 “xxlbmst” usrw_key2 Label Name usrw_key3 Site usrw_key5 Label Description usrw_datefld[1] Last Change Date usrw_intfld[1] Default Printer Number usrw_intfld[2] Default Print Quantity usrw_intfld[3] No. of Fields on Label usrw_intfld[4] Default Number of Duplicates usrw_charfld[1-15] Label Fields usrw_logfld[1] Active Template (Y/N)

Index (AmSafe Example) Label Master (xxlbmst) Indexing with the One-To-One, Primary Unique Key usrw_index1 usrw_domain “CPUSD” usrw_key1 “xxlbmst” (Custom Table Name) usrw_key2 Label Name

Index (AmSafe Example) Label / Item X-REF (xxlbxref) Indexing with multiple indexes, while retaining unique key requirement. usrw_index1 usrw_domain “CPUSD” usrw_key1 “xxlbxref” (Custom Table Name) usrw_key2 Sequential No. (Calculated & Stored) usrw_index2 usrw_domain “CPUSD” usrw_key3 Item Number usrw_key4 Customer Number

Using the Table- Creating Records (Progress Programming Example) FIND FIRST usrw_wkfl WHERE (usrw_key1 = "xxlbmst") AND (usrw_key2 = CAPS(inp_template)) NO-ERROR. IF NOT AVAILABLE usrw_wkfl THEN DO: CREATE usrw_wkfl. ASSIGN usrw_key1 = "xxlbmst“ usrw_key2 = CAPS(inp_template) usrw_key3 = inp_site usrw_key5 = inp_label_desc usrw_domain = global_domain usrw_intfld[1] = inp_prt_no usrw_intfld[2] = inp_prt_qty usrw_intfld[4] = inp_prt_dups usrw_intfld[3] = inp_fields usrw_logfld[1] = inp_active usrw_datefld[1] = TODAY. DO ws_idx = 1 TO 15: ASSIGN usrw_charfld[ws_idx] = inp_label_field[ws_idx]. END.

Using the Table- Lookups & Updates (Progress Programming Example) FIND FIRST usrw_wkfl USE-INDEX usrw_index2 WHERE (usrw_key1 = "xxlbxref") AND (usrw_key3 = CAPS(inp_our_part)) AND (usrw_key4 = CAPS(inp_cust_number)) AND (usrw_key5 = inp_site) NO-ERROR. /******************************************************/ /* If record is found, fill the screen variables. */ /******************************************************/ IF AVAILABLE usrw_wkfl THEN DO: ASSIGN ws_seq_no = usrw_key2 inp_our_part = usrw_key3 inp_cust_number = usrw_key4 inp_domain = usrw_domain inp_site = usrw_key5 inp_table = usrw_key1 inp_active = usrw_logfld[1] inp_prt_no = usrw_intfld[1] inp_prt_qty = usrw_intfld[2] inp_prt_dups = usrw_intfld[4] inp_last_change = usrw_datefld[1]. DO ws_idx = 1 TO 6: inp_template[ws_idx] = usrw_charfld[ws_idx]. END. END. /* IF AVAILABLE usrw_wkfl */

Using the Table- Linking Multiple User Work Table Records (Progress Programming Example) DEFINE BUFFER usrwwkfl FOR usrw_wkfl. FIND FIRST usrw_wkfl USE-INDEX usrw_index2 WHERE (usrw_key1 = "xxlbxref") AND (usrw_key3 = CAPS(inp_our_part)) AND (usrw_key4 = CAPS(inp_cust_number)) AND (usrw_key5 = inp_site) NO-ERROR. /******************************************************/ /* If record is found, fill the screen variables. */ /******************************************************/ IF AVAILABLE usrw_wkfl THEN DO: FIND FIRST usrwwkfl NO-LOCK WHERE (usrwwkfl.usrw_domain = usrw_wkfl.usrw_domain) AND (usrwwkfl.usrw_key1 = “xxlbmst”) AND (usrwwkfl.usrw_key2 = usrw_wkfl.usrw_charfld[1]) NO-ERROR. IF AVAILABLE usrwwkfl THEN DISPLAY usrwwkfl.usrw_key5. END. /* IF AVAILABLE usrw_wkfl */

Review & Summary The User Work Table (usrw_wkfl) * We looked at the Table itself * We looked at the Indexes provided * We looked at the “Benefits” * We looked at the “Gotchas” * We looked at some code to get you there … … Provided in Standard MFG/PRO … Does NOT require .NETUI … No Schema Changes … No 3rd Party Software Required … Easy to do yourself, No Consultants Required … It’s FREE

Questions? Contact Information: Email:gsevison@amsafe.com Phone:574-970-2644

Hidden Rooms & Secret Stairways: