Custom Database Tables

Posted By Darren Jerrard over 3 years ago

I've done a little work with User-Defined Tables (UDTs), but I've read they have limitations: Single-column primary keys and no support for indexes.

If I want to use tables that don't have these limitations, what is the correct approach? I come from a Sage 300 background where there was an API for creating your tables 'the Sage 300 way'. This had the benefit of including your tables in Sage 300's Data Dump & Load processes.

Is there something similar for Sage 100? If not, is it expected that we would just use SQL to create our own tables in the Sage 100 company database (for ease of backup/restore) or not put them in the Sage 100 database?

Thank you for your guidance.

Parents

0 Kevin M over 3 years ago

Use concatenation for your UDT key field to string multiple field values together (using a unique separator like "^" or "~").

Either that or consult with a Master Developer to get a custom enhancement.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Darren Jerrard over 3 years ago in reply to Kevin M

Kevin,

I've seen that other developers use the concatenation technique and, assuming a good concatenation approach, that would help with the primary key part of my question. But what about multiple indexes? This seems like a very basic thing that many integrators would require.

I don't think I've seen the title 'Master Developer' appear in any of my readings. Where would I find the resources that gives one the education to get such a title?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Darren Jerrard over 3 years ago in reply to Kevin M

Kevin,

I've seen that other developers use the concatenation technique and, assuming a good concatenation approach, that would help with the primary key part of my question. But what about multiple indexes? This seems like a very basic thing that many integrators would require.

I don't think I've seen the title 'Master Developer' appear in any of my readings. Where would I find the resources that gives one the education to get such a title?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

0 Kevin M over 3 years ago in reply to Darren Jerrard

Sage will have to answer the question about how to become a Master Developer.

(Since I'm not a MD myself, I can't answer the index question either... I think you could add that in SQL if using the Premium version, but for a Providex back end, I don't know what's possible).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Darren Jerrard over 3 years ago in reply to Kevin M

Kevin,

I appreciate your responses. The differences in the tiers regarding data storage is certainly unusual!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Kevin M over 3 years ago in reply to Darren Jerrard

Sage 100 was developed originally with a Providex back end. Years later a SQL back end option was added (although the core program is still written in Providex), which is the "Premium" version.

"Standard" is the base version (originally MAS90), with no client-server architecture (where workstations access the data files directly). "Advanced" (originally MAS200) is almost the same as Standard, but with a service for client-server performance benefits (necessary for higher user counts).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 BigLouie over 3 years ago in reply to Kevin M

MAS200 was originally MAS90 C/S that goes way back
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Kevin M over 3 years ago in reply to BigLouie

BigLouie knows the history far better than I do. (I've only been working with Sage 100 for about 8 years now).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 100

Custom Database Tables

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING