On Mar 8, 2017, at 8:34 AM,Pat Bensky wrote: > Yeah I did look into that option as well, but it doesn't solve the trigger > problem. Triggers are essential to us!
Here is an idea: Create the number of tables that you want for your users to be able to use, but only include 1 field for the primary key for each user table. Set the trigger options and add the trigger code. This is your starting point. Have another table that you maintain that contains the structure definition of all these user tables. Maybe with a name like "UserFieldsDefinitions”. Provide a nice UI so the user can create the fields that they want in each of these user tables. Store all this data in UserFieldsDefinitions. then use SQL to create the fields in the user tables. When you send them a new structure file have some code in On Startup or On Server Startup to check the user tables and compare the fields to what you have stored in UserFieldsDefinitions. Probably only need to check if there is 1 field in a user table. If this is true then you know this is the first time this data file was opened with this new structure file. Now use the data in UserFieldsDefinitions to run all the SQL code again and create all the fields in the user tables. You will need to create the fields in each table in the exact same order as before. Now you have recovered your “missing fields” for each table. And they are in the same order as before. You will have to test this to make sure it works. I know that each field is given a UUID when you create it. That UUID would change each time you create the field using SQL. But I don’t think the field UUID comes into play when referencing fields in method code. I think they are still internally referenced by their field number. But you’ll need to test this entire process to ensure you don’t lose any existing field data when you update the structure and recreate the fields. This would allow you to stop providing 500 fields in each of your user tables. And it would remove any limitations on how many date fields, text fields, real fields, etc. that are available in each user table. User can create as many as they want of each type in each user table. One thing that needs to explored and tested is deleted fields. Do you allow users to delete fields they create in user tables? If they delete “field 4” and then later create new field what will be the field number of the new field. Should it be 4 again, or should it be another number. How does that effect the data in the user table? Maybe you don’t let them delete any fields. Only thing they can do is make them invisible. Could be some issues here that can only be found by doing some testing. Tim ******************************************** Tim Nevels Innovative Solutions 785-749-3444 [email protected] ******************************************** ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
