Read only table
I would like to make a table accept data, but once entered it can't be altered, Any ideas?
I find that written by Steven, Work for me in some specific cases, but not for all the table.
Let's say we make use of a seperate table for settings with only one record called SETTINGS and a Yes/No field called EDITTABLE.
Then you can put this in the "writable if" section of the desired fields you want to protect in the other tables.
In order to use a database instead of a written log for Firearm sales, the the Feds require that information can not be altered, once entered. This is a relational DB using 17 tables (and subtables) to create three types of invoices (Transfers, Sales, Auctions), customers files, wholesalers, manufactures, calibers. auctions, dealers, inventory, etc. It's a good working system and I can make the table unalterable, but that prohibits the entry of new items. Hence the need for a table that can be added to but not altered.
Ok, well I don't know if the feds would consider this acceptable. You can use the Writable if section of a table. If you create a yes/no field called Edit. You can set the Writable if to "Edit" and it will only allow writing to if the Edit field is set to Yes.
But if this is on the Mac app, anyone with access to the DB can set that field to Yes. If this is in the cloud you can add other tests like user account.
So you would:
1) create a new yes/no field called edit in the table you want to "protect".
2) then modify your script that copies data to this protected table to include this at the beginning:
first((select TableName).(Edit := 1))
3) then at the end put:
first((select TableName).(Edit := 0))
I hope this helps.
I added this to the first entry
first((select Logbook).(Edit :=1))
first((select Logbook).(Edit :=0))
and get the message, above of "End expected: Manufacturer at line 2, column 12"
Sorry to be so dense, but what am I doing wrong?
first entry is just selected from the Table Manufacturer as Make
I thought you were copying existing data from existing tables into the Logbook table, then you want to lock the table.
The example you gave shows you are referencing fields in other tables. Which means data can be changed in Logbook by changing data in the other tables. I would think you would have to copy the data into number and text fields so you can say data is not changed by other tables.
You also have an invoice table. I'm guessing you need to make sure you have a record of your invoices that you can say to the Feds are not changeable. From my current knowledge of your structure, you need to copy every invoice into your Logbook table. Is that correct?
The invoice is created after the fact and doesn't need to be unchangeable. After the invoice is made, I go back to the logbook and enter the date the item was removed. That completes that entry of the log book.
I have tried to create another table that would extract all the data from the log using only the serial number. That could become my permanent record, but all I can get are concatenated lines that aren't in nice columns that are easily readable and make terrible printouts as I need them Biannually. If I could create another table that would extract the required data into nice columns, that would be perfect and satisfy all requirements as I would add "null" to the "writable if" window.
So Logbook table is the one that needs to be write protected?
If invoices are created from Logbook then it sounds like you are constantly making edits to Logbook as you add/sell items. Are you right now going into the Logbook table to add items and change "sell" date?
To me is sounds like a possible way to move forward would be to:
1) create a Dashboard (a separate table) that has a section that you can enter in new items. So it would have fields for serial number, manufacturer, make, model, etc. Then you would push a button to add the new item to Logbook.
2) in another part of the dashboard (by either scrolling or a new tab) you can search for an item by serial number then set a remove date, then push a button that would then set the date in Logbook.
This way you are not interacting with Logbook directly and can be sure that it is locked.
You would still enter in data in your other tables directly. Or you can add to your dashboard.
I've never used a button before. I guess I'm not taking advantage of all the capabilities of this program. I'm self taught here, so forgive my ignorance. I will have to review the manual again. Thanks for the info, I appreciate the time and effort you have made in replying to my problem. This sounds like a solution.
Most of us are self taught, so welcome to family. I won't bother with the manual. It is only good as a reference. I've learned most of knowledge from the Nioxus videos and this forum.
I've mocked up what it could look like. It is very rough but it gives you an idea of how it could work.
I've recreated the fields from other tables in Logbook, so I can copy the data directly into it. If I created reference fields then when you are in Logbook, even with it set to not write, you can still modify data in other tables. Which I would guess would be a no/no from the Feds.
It is currently setup to clear out what you enter in the dashboard.
The system table is to load Jacques tools like autocomplete. If you want to use it, just open the table then the tools will be loaded. You can ignore it or delete it as well.