Need help building a many "Code" functionality.

[dmontano]

Hello all,

I have been working on an area going on two weeks to try as hard as I can to figure out how to do what I want. I have decided to make and provide:

1. stripped-down database file
2. data model pdf
3. screenshots

In my previous requests for help I struggle making my "case or problem" clear and I probably frustrate those willing to help: I hope this attempt is much better.

I have tried color-coding to match Entity Type to aid in understanding:

1. Relationship Graph
2. Layout Header
3. Data Model PDF

I have colored fields on layouts in BLUE as they are the objects that apply to the area I need help with.

Screenshots show:

1. Custom Functions
2. "Entity Code Type" join records
3. Calculation Fields - within the same table (what I can do, but do NOT want to do this in each table)
4. List of Code Types
5. Scripts involved via Script Triggers

 
Goal:
To create "codes" for individual records within any table that I have a need for codes. 

What do I mean by codes? I will give some examples:

• INV-4255 (Invoice with unique number 4255)
• PO-3244 (Purchase Order with unique number 3244)
• PRS-468 (Person with unique number 468)
• ORG-588 (Organization with unique number 588)
• SAL-EST-WOR-200 (Sales Estimate Worksheet with unique number 200)
• SAL-EST-WOR-ORD-200 (Sales Estimate Work Order with unique number 200)
• MPN-4255348 (Manufacturer Part Number with unique number 4255348)
• ACM-MPN-4255348 (Acme as reseller of Manufacturer Part Number with unique number 4255348)

It is common to see this "use-style" of prefixing a unique number of a record with an alphabetical "code". I have observed this in many cases and this convention "lines-up" with the "Entity Types" or tables I have in my database to serve as the prefix mechanism: Invoice = INV; Purchase Order = PO; etc.

An "invoice" Entity Type can be abbreviated to "INV" and then I can add just the "number" portion of a serial ID (if numbers are being used in the primary key).

I really do need codes: I am certain. I have made sure I was not falling into a rabbit-hole for something that was not "needed". I can go on with additional business cases for this need, but I hope the above case is valid enough to merit the cause.

Some terminology I use:

• ENTITY TYPE = a Table
• ENTITY = a Record

I actually have a table specifically called "Entity Type" that contains one record for each table in my database. I like it: I can leverage it when I need to "constrain" or "filter" data for use within any of the Entity Types I have in the database. I mention this, as I am using this also in this "problem area" that I have.

The following "Entity Types" are in need of "Codes". Each Entity Type can have more than one code:

• Bindery Operation Type
• Brand Type
• Entity Type
• Entity Type Purpose
• Entity Structure Type
• Year

I have chosen these Entity Types as sample material because they are relatively simple and easy for me to strip out of my database. I could have just left one Entity Type, but leaving a few others can show what I have done in terms of calculations shown on each layout.

On the layouts of these Entity Types, I do show the codes being calculated with Custom Functions. I made the Custom Functions together for consistency purposes.

What is important is showing the fact that these are fields specific to each Entity Type - which is what I do NOT want. This is an obvious case of needing an Entity Type to contain MANY codes for an Entity. So...

I have a Entity Type that will facilitate the storage of many codes for ANY Entity Type. That Entity Type is called:

• Code

I have another Entity Type to differentiate the different Code Types that I want to select from in order to apply the Code Type "recipe" to a particular Code. That Entity Type is called:

• Code Type

In order to constrain which Code Types are applicable to an Entity Type, I created a join Entity Type called:

• Entity Code Type

Custom Functions:
I created 9 Custom Functions. The calculations work for my example, however, I am sure the code I created can be optimized or better written, etc. 

Scripts (triggered)
I created 7 Scripts to just "display" some result in my target field. I figured out how to get a hard-coded test result in the target field, but can not figure out how to get the Custom Function result in the target field.

Right now, I had to create 7 essentially the same script, but with subtle differences. I really would like one script that branches according to its use location. I thought if I could get one script to work - I could then try and figure out how to combine in one script.

If I could get some help on this I would greatly appreciate it!

code_help_20240925b.fmp12 code_data_model_v1.pdf

[Ocean West]

I think this would suffice. https://scalefm.com/sequence-generator/

 

 

[dmontano]

Hi Stephen,

Thank you for the link: I read the article and downloaded the file.

It certainly addresses the topic, so it's nice seeing external validation that the need exists in other people's mind too. I'm not able to easily see how I could retrofit or apply the workings of that file to my "set-up", but that is my limitation as of today. It is something I can chew on.

I have figured out how to do what I want unrelated to the linked article and file mentioned above.

However, my script is huge and unwieldily: I will try as best I can and post back here for help.