Global script to merge records of multiple tables

If the "Corporate Table" is a way for people to view data then may I suggest the use of Dashboards.

 

Basicaly a dashboard is a empty table that is linked to other tables so you can do summaries or views of data in other tables. For example I have a sports database, so I have quite a few dashboards that allow me to view my summary details of my "data" tables. In fact, I rarely touch my raw data tables anymore as everything I need gets done through my dashboards.

 

To create a dashboard:

1) create a new table, name it, add text field for now

2) open the new table, add a record

3) create a new Form view, name it Dashboard

4) delete the "all" view so your dashboard opens up to the form view

 

Now you have a table that you can put View elements or create fields that can look into other tables and summarize your data. For example for Customer1, you can create a View element that shows them data from customer1 table.

 

Hope this helps.

If we are talking about tables in the same database, then wouldn't your suggestion also have the same security issues? I use the app version so I'm not very familiar with permissions. If you can secure a table on a per user basis then you can also do that with dashboards.

 

On a practical level, the filtering issue will not go away just by giving users access to their own tables. It also sounds like you have to do a bit of manual work by creating a new table for each new customer any ways. Then updating your buttons/triggers to take into account the new table names. Then if you modify a field or add new fields to a customer table do you have to then add it to all of your other customer tables?

 

Re-reading your question, I didn't process that you want to create a super Corporate table. Now I wonder what do the Corporate users need to do to the data? Are they only looking at aggregate data? If so then that would support my suggestion for dashboards.

 

Another thought is why does each customer have their own table? If the data stored in each table is the same then they should be in one table. A new table should only be used when data not related to each other needs to be stored, e.g. people names vs product info vs invoices.

 

But if you want to create a super table you have to start looking into the loop function. Here is a basic format:

 

for loop1 in select Table1 do
loop1.(
let xCreate := (create Corporate);
xCreate.(field1 := loop1.field1);
xCreate.(field2 := loop2.field2);

etc...
)
end

 

That is what you would do the first time. I don't know if you can stack loop fields.

 

Then you need to create a system to allows for you to check for new records. You would then use that system to limit your select so it only pulls up the new customers.

 

for loop1 in select Table1[newcustomers = true] do

 

Then add a section to set the field to false:

 

loop1.(

loop1.newcustomers := false

 

Now you can see where having a table for each customer is problematic as each new customer table forces you to manually go in and change your formulas to account for the new table. If all of your customers were in the same table then your formulas won't need to be manually updated everytime a new customer is added.

 

Good luck and let us know how things go.