Regular Screen to update a Code Table

[DeechVogt]

Here is the Issue:

We have a SB+ Code Table which requires developer/system administrator access level in order to be maintained.  Now we want to put this one particular code table administration into the hands of the user who know, use and change the data when required. 

The problem is many of our Benco custom developments now use this code table to validate against.  My mission is to provide a maintenance screen for the user to use to update the code table record, but not break the code table record for our custom developments and SB+ validations.

Any help or suggestions would be appreciated.

Thanks,

Deech Vogt
Benco Dental

[Tom Pellitieri]

Assuming the user can't access the regular process (i.e., TABLE.DEFN), you could always write a separate process that requested the appropriate information and updated the Code Table record.  The file layout is fairly obvious if you examine any of your existing code tables.  The data is normally stored in xxDEFN, although you could also set up a separate file for them. 

You can use a separate file anyway, since TABLE.DEFN requests the file name to start.  You could use EXEC "TABLE.DEFN MYFILE" in a paragraph to force MYFILE to contain the definitions.

Hope this helps.

--Tom Pellitieri

[DeechVogt]

Tom,

Thanks for the suggestions.  As it turns out this definition is in our own file CONTROL.BDS.  To get more specific, I guess what I don't understand is the exact purpose and structure of multi-sub-valued field 6.  Which looks like, if had to venture a guess, a way of supporting both upper and lower case matching on searches.

Fields 7 - 10 also have some values which concern me.  I have looked through all the documentation including some additional technical documentation Kevin has and could not find a layout for a CONTROL TABLE Definition.  I am afraid I could break the SB Table lookup functionality if I don't update the record correctly from my regular maintenance screen.

Here is the record: REGION.BDS from CONTROL.BDS:

Code:

001: CT                                                                         
002: REGION NAMES                                                               
003: ALLEGýBISCAYýBLURDGýBUCKEYýCAROLIýCHESAýCHICAGýCRPACTýDALLASýDELVALýDERBYýE
MPIREýGWAYýGRLAKEýGULFCOýHUDSONýKNOXýLONGISýLOSTARýMETROýMISCýMISSIPýMOTOWNýNEWE
NGýNSTARýNOVAýPEACHýPIEDMýROCKYýSUNSHIýSUSQUýALL
004: ALLEGýBISCAYýBLURDGýBUCKEYýCAROLIýCHESAýCHICAGýCRPACTýDALLASýDELVALýDERBYýE
MPIREýGWAYýGRLAKEýGULFCOýHUDSONýKNOXýLONGISýLOSTARýMETROýMISCýMISSIPýMOTOWNýNEWE
NGýNSTARýNOVAýPEACHýPIEDMýROCKYýSUNSHIýSUSQUýALL REGIONS                       
005: ALLEGýBISCAYýBLURDGýBUCKEYýCAROLIýCHESAýCHICAGýCRPACTýDALLASýDELVALýDERBYýE
MPIREýGWAYýGRLAKEýGULFCOýHUDSONýKNOXýLONGISýLOSTARýMETROýMISCýMISSIPýMOTOWNýNEWE
NGýNSTARýNOVAýPEACHýPIEDMýROCKYýSUNSHIýSUSQUýALL                               
006: E;CALLEGüBISCAYüBLURDGüBUCKEYüCAROLIüCHESAüCHICAGüCRPACTüDALLASüDELVALüDERB
YüEMPIREüGWAYüGRLAKEüGULFCOüHUDSONüKNOXüLONGISüLOSTARüMETROüMISCüMISSIPüMOTOWNüN
EWENGüNSTARüNOVAüPEACHüPIEDMüROCKYüSUNSHIüSUSQUüALLüALLEGüBISCAYüBLURDGüBUCKEYüC
AROLIüCHESAüCHICAGüCRPACTüDALLASüDELVALüDERBYüEMPIREüGWAYüGRLAKEüGULFCOüHUDSONüK
NOXüLONGISüLOSTARüMETROüMISCüMISSIPüMOTOWNüNEWENGüNSTARüNOVAüPEACHüPIEDMüROCKYüS
UNSHIüSUSQUüALL;CALLEGüBISCAYüBLURDGüBUCKEYüCAROLIüCHESAüCHICAGüCRPACTüDALLASüDE
LVALüDERBYüEMPIREüGWAYüGRLAKEüGULFCOüHUDSONüKNOXüLONGISüLOSTARüMETROüMISCüMISSIP
üMOTOWNüNEWENGüNSTARüNOVAüPEACHüPIEDMüROCKYüSUNSHIüSUSQUüALLüallegübiscayüblurdg
übuckeyücaroliüchesaüchicagücrpactüdallasüdelvalüderbyüempireügwayügrlakeügulfco
ühudsonüknoxülongisülostarümetroümiscümissipümotownünewengünstarünovaüpeachüpied
mürockyüsunshiüsusquüall;ZX
007: ý1                 
008:                     
009: 19.1ýDCMý14663ý29963
010: PRJ*ERL19022       

Thanks,

Deech Vogt
Benco Dental

[Tom Pellitieri]

Here's what I can tell you about the Code Table attributes:

1: Definition Type (CT = Code Table)
2: Table Description
3.M: Internal Code
4.M: Display Text
5.M: Input Code
6: Input/Internal Map (see below)
7.1: Allow Null (1=Yes, 0=No)
7.2: Case Insensitive (1=Yes, 0=No)
8: (not used)
9: Last Modification (Version, UserID, Date, Time)
10: ?? Update Tracking ??

We use Prelude, so on our system, "Version" is taken from MD item $ADS.RELEASE attribute 2.  Also, if there is a value in Attribute 10, it begins with PRJ*, so I suspect it's used by their Project Management Tools.

Attribute 6 has the following format:

E;C[internal.values];C[input.codes];ZX

For [internal.values] and [input.codes], each value is listed, separated by sub-value marks (ASCII 252). 

If there are no Input Codes specified, the Internal Values are used.

If Case Insensitive is "Yes", both sets are duplicated, with Input Codes in both Lower and Upper case.

If all codes are Length 1, no sub-value marks are used.

So, if Internal Codes 1/2/3 are selected by entering A/B/C, respectively, Attribute 6 would read:

E;C123;CABC;ZX

If this is Case-Insenstive, Attribute 6 becomes:

E;C123123;CABCabc;ZX

Hope this helps somewhat...

--Tom

[DeechVogt]

Tom,

Yes, Yes, Yes, that is exactly what I needed.  Thank you!

Where did you find this information?  Just so next time I might know where to look?

Thanks,

Deech

[Tom Pellitieri]

Where did you find this information?

I didn't exactly "find" the information about Code Table layouts - I reviewed the existing tables, plus I examined a test table with various parameters to see how the data item was affected.

Please note that it appears more recent versions of SB+ (5 and up??) always include the conversion items in Attribute 6.  Older versions appear to only have used it when necessary, based on the tables in my system...

--Tom

[DeechVogt]

Tom,

Again thanks.  Sorry, this was something I should have done myself.  ( ) I did examine the record, but the thought of building my own test code table to find out what everything else did just didn't occur to me.

Thanks Again,

Deech