Search for tables with part of their name or with certain conditions
I have a mac database and I will need to create from a button a separate table for each customer in my database (the list of customers is another table). Once the different tables are created, later I will need to access each table indicating somehow part of the table name and the conditions that the table must meet (the customer code) for ninox to find it. Is it possible to search for tables using a text box or button?
And how can the tables be created from a button?
Thank you very much.
10 replies
-
Hi there -
You can't create a new table with a button.
Which brings me to a question, why would you need a new table for each customer?
Maybe if you gave us more detail on what you are trying to achieve we can give you some ideas.
-
Thank you for answering Fred. The reason is that I want to avoid making a single overly large table that includes all customers. The table is intended to be an accounting journal of the customers and so it is going to be very large.
I could create tables manually, but I also have another problem that I only know how to set up the search engine for one table: I can use SELECT 'table01' if I have customer 01 selected in another selection field. But if the selected customer is 02, I would have to add another SELECT for the new table and so on for each customer.
So, I never finish designing the database. If there is no choice or any other idea, I will have to use a single table for all customers very very long... -
I'm guessing you will have lots of customers so I think having to manually create a new table for each customer would be more of an burdan than having a large, or very large table. Databases are supposed to be large, 100,000+ of records in a single table. I can't say I've dealt with a DB that has a record that large on the Mac version. In addition, databases are ment to handle large number of records but does not handle large number of tables very well. I can't imagine what the side panel would look like with 20+ tables or 100 or 200.
If you continue the path of a new table for each customer, one idea is to match table name to the customerID. I don't have my computer right now, but I image you could do something like:
let xTable := this.Id
select xTable
Maybe someone else can verify if I'm correct or not.
Again, I would vote for a large "history" table. Don't be afraid of big tables. One thing about Ninox, and probably many DBs, that I am learning is that I rarely interact with my raw data table directly. I have a sports league and all of interactions with the raw data is done through dashboards.
-
Hi.
How many customers do you have?
Regards John
-
I was wrong about putting the table name in a variable. It looks like you can't.
-
If you make use of the eval() function, you can use tablenamevariables.
Steven
-
I don't think it would be good practice to use a seperate table for each cutomer.
Nearly all accounting software stores the customer/supplier info in one main table and then of ciurse use linked tables to for the ledgers and audut trail.
From coding perspective i think you will by maing it grossly over complications for no rel benefit.
I'm using tables (with linkeed child tables) that have over 600,000 records in one table and it works fine
and is a good deal easier to deal with than if it was split into many tables.
I think you will keep hittin hurdles with so many tables.
ie what if you want to have a button with some script to do an action on the record!. Your going to have to rpelaicate this button and code on every table etc.
.
-
ok. Thank you very much. 600,000 records is a lot so I'm thinking I should change the database design and use a single table.
Anyway, I have found that the openTable function in a button does allow to open tables by typing their name in a text field. It will be useful for me to put it in a menu and manage all the tables I already have.
But apparently it is difficult to use other functions (SELECT, openRecord) using the table name as a text string. Or at least that's my understanding after reading this:
https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/how-to-assign-a-tables-identifier-to-a-variable-i.e.-not-hardcode-table-names-in-functions-5e32493f12eb3a4d2a455700About the eval function that Steven tells me about, I really don't know what the syntax would be.
Regards.
-
Example code for eval() where I want to delete records which are flagged in a Yes/No field. The table name is selectable with a choice field named 'Select a table' where the choices are the tablenames.
let tablename := text('Select a table');let code := "delete(select " + tablename + " where flag = true)";eval(code,this);Steven.
-
ok. I have tried the code on a button but it doesn't delete anything. It also doesn't work if I put it in a view field. Maybe I'm doing it wrong.
But it does work if I put it in a formula field:
let tablename := text(seleccion);let code := " (select " + tablename + " where flag = true).nombre";eval(code, this)And it returns me the text of the 'nombre' field adding brackets and quotes:
["Record 1"]Regards.
Content aside
- 2 yrs agoLast active
- 10Replies
- 1012Views