Updating existing data

Hi
I have posted this issue at the end of a previous thread but not got a response yet so starting a new thread. I really need to resolve this to continue with my application.

I have a "View Projects" menu option that links to a page that is generated from a view in mysql. This view joins information from a main technology table (techid, techname, date created, inventor foreign key, project manager foreign key, organisation foreign key etc.) with information from sub-tables such as the inventor table (inventor name), project manager table (project manager name), organisation table etc..

The "Add Projects" menu option links to a page that enables me to add new information to the technology table. The form used to add new technologies also takes info from other tables via the select single menu options. So for example if I want to add the project manager details to the technology table for a new project, I select the project manager name from the project manager table but only add the project manager foreign key to the technology table.

I would like to add an "Edit Project" menu option. This would bring up all the details that appear in the "View Project" menu option and enable me to modify these details using the same logic as for the "Add Projects" menu option. I don't understand how I can do this given that the information in the "View Projects" menu option is derived from a mysql view (i.e - is read only).

Thanks

Your current DaDaBIK version​

You are using DaDaBIK version 11.3-Elba pro, installed on 09-03-2022 (installation code: 178066228c557cae36), the latest version of DaDaBIK is 11.6-Elba released on 14-06-2022

You are not running the last release of DaDaBIK, the release you are running might have bugs and security holes, see the official change log for further information. You can upgrade DaDaBIK here.

In case you want to upgrade to a more powerful edition (from Pro to Enterprise/Platinum, from Enteprise to Platinum) please contact us.

System info​

PHP Version: 7.4.16

mysql version: 5.7.24

Web server: Apache/2.4.33 (Win64) OpenSSL/1.0.2u mod_fcgid/2.3.9 PHP/7.4.16

Client: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.0.0 Safari/537.36

URL installation: http://localhost:81/Dadabik/
 

eugenio

Administrator
Staff member
Hello,
I am not sure if I have understood the whole scenario, but I'll answer to the last part of your post since it can be useful.
The fact that VIEWS are read-only is a misconception: you can insert and update in a VIEW (the corresponding table will be affected) if it meets some requirements; for mysql these are the requirements:

Best,
 
I've just checked and the IS_UPDATEABLE flag is set to Yes for the view in question in the INFORMATION_SCHEMA.VIEWS table so it looks like the view is updateable.
I assume I just need to change the Edit permission from no to yes to enable me to update the view.
I'll do some experimenting.
Thanks
 
Just tried updating the view using phpmyadmin and its not letting me update the view even though the IS_UPDATEABLE flag is set to Yes.
I am getting the following error.
#1288 - The target table view_technol of the UPDATE is not updatable
I assume I need to get to the stage where I can update the view in phpmyadmin before trying to do it in Dadabik?
 
None of the views I have created are updateable even though I think they all meet the rules for updateability.
I am trying to solve this issue first for my simplest view which joins information from a company table with information from a country table - see script below used to create the view.
I am wondering if it is something to do with this error message that phpmyadmin is displaying
"Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available."
The view does not have a unique column. Is it possible to set a unique column on a view and how should I edit the script below so that COMPANYPK is unique?

CREATE VIEW view_company
AS
SELECT
company.PRIMARYKEY AS COMPANYPK,
company.NAME,
company.ADDRESS1,
company.ADDRESS2,
company.ADDRESS3,
company.CITY,
company.PHONE,
company.WEBSITE,
country.NAME AS COUNTRY
FROM company
LEFT JOIN country ON company.COUNTRYFK = country.PRIMARYKEY
ORDER BY company.NAME ASC
 

eugenio

Administrator
Staff member
You are using an OUTER JOIN (LEFT JOIN is the short version of LEFT OUTER JOIN), which is one of the cases where VIEWS are not updatable.
 
Ah - ok, I hadn't realised that, thanks
In that case I seem to be back to square 1 - how do I update existing data?
As a simpler explanation, I have a contracts table which has columns such as contract_ID, contract_type, party1, party2, status_fk etc.
status_fk is a foreign key that links to a status table which has a status column with entries such as negotiating, signed, unsigned, terminated, expired etc.
I am using a view to show all the details of the contract to the user, so all columns from the contracts table and the current status of the contract (using a left join with the status table).
If a contract has been changed, I want to display all the details of the contract to the user (using the view) but let them update the status (from say unsigned to signed).
 

eugenio

Administrator
Staff member
Maybe I am missing something but I don't think you need a VIEW, if you set status_fk as select_single lookup field and you show, as linked field, the status (from the status table), users will be able to change the status and to see the status itself from the contracts table.
 
Yes - that is how I am doing it on the Add Contracts page - when adding a new contract. Let me change the View Contracts page so it is based on the contracts table rather then the view and see if I can get it to work this way.
Thanks
 
I have done the above and changed the permissions so I can now edit the information in the main Agreements page :)
However the system is no longer presenting data how I would like it to.
In the main results page, I only want to display the Agreement ID and the Agreement Name and for these fields not to be editable (to keep the display simple when the user is searching through all agreements).
In the details page for each agreement, I want to display all the fields and for these fields to be editable (Agreement ID, Agreement Name, Agreement Type, Party 1, Party 2 etc.).
Is there any way I can set the permissions so that you can only edit the information for each agreement when you are displaying the details page for that agreement / contract?
 
Top