0

Global script to merge records of multiple tables

Hi,

I want to open the database to customers and give them access to their own table (Table customer1, Table customer 2, etc.). 

I would like to create a script that merge all those tables in one "Corporate Table" for corporate users (instead of export table 1 and import in the corporate table, which is very time consuming) : how can I do that ? I can't find a way on the forum to manage that.

And it would be even better if the script search in all the tables so that if there is a new customer (and a new table) they don't have to call me every time.

I'm sur Ninox can do that....

Thank you!

3 replies

null
    • Fred
    • 3 yrs ago
    • Reported - view

    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.

    • LoResah
    • 3 yrs ago
    • Reported - view

    Hi, thank you but it doesn't help, it's not secured enough and not practical enough, I would have to administrate it myself.

    If I do that I need to create too many views (one for each customer) and as far as I have tested Ninox with corporate users, they can change the filters in the views or if they forgot a field then they can't see the record.

    • Fred
    • 3 yrs ago
    • Reported - view

    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.

Content aside

  • 3 yrs agoLast active
  • 3Replies
  • 580Views