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.

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?

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).

Kevin,

I appreciate your responses.  The differences in the tiers regarding data storage is certainly unusual!

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).

MAS200 was originally MAS90 C/S   that goes way back

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

Concatenation plus a UDF field to hold each individual value used in the key works fine for most implementations of UDTs that I've done.  UDTs do indeed only get created with the single primary key which I believe would still be indexed. 

Even on Standard and Advanced, I've worked with a UDT that has millions of records and still performs well when writing to it and with a custom report that queries from it using criteria on the fields used to hold the individual key values instead of the applying the criteria against the key column but this is only because the UDT is the only table in the report.  If I were to introduce an additional table to the report, regardless of join, performance drops dramatically.  I know the ProvideX ODBC driver gets a bad rep for having poor performance dealing with large tables but I've found this really only applies when there is more than one table included in the query.  

If you are on Premium, the data is going to being in a SQL table and Kevin can probably correct me if I'm wrong since i don't work with Premium much but i would not expect performance to be an issue even if you had a report that joined the UDT and another table since it would be using T-SQL.  I think at this point, the performance would come down to the hardware specs and available resources on the server where the Premium database resides.  

Custom report performance with SQL is always better.  Forms with SQL don't have the same benefits because the Sage 100 program still has to produce the work / temp table data.