Record refusing to update

[darren]

Hello,

I have an error with dadabik, and I am not sure what is causing it and how to solve it.

In short the record I edit is refusing to update when I make changes, I have included screen shots of what happens when I try to edit the field.

First I choose edit on the field I want to change

Then I want to change Null1

I have changed it to Test and then I click Save

However I am returned to the search page and the record is unchanged

And when I click to edit the same record again, I get an error saying that the record is locked by me so I am unable to edit

I am forced to manually edit any of these record through phpmyadmin or heidisql or some other direct mysql editor

As a side note, these records have at least 1 record on another table that is using it a foreign key and some of these tables have more than 5 records that depend on it, I dont know if that is relevant but I wanted to tell you in case it is.

You are using DaDaBIK version 8.1-Lerici Pro, installed on Nov 7, 2017 (installation code: 130575a022f599edc0), the latest version of DaDaBIK is 8.1-Lerici released on Sep 5, 2017

You are runnning the last release of DaDaBIK

PHP Version: 7.1.12-1+ubuntu16.04.1+deb.sury.org+1

OS: Ubuntu 16.04 LTS server

mysql version: 5.7.20-0ubuntu0.16.04.1

Web server: Apache/2.4.18 (Ubuntu)

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

 

[eugenio]

Hello,
a couple of questions:
1) is microchip the PK of the table? Maybe you set a referential integrity constraint that doesn't allow you to update? (it wouldn't allow to do it in phpmyadmin too, though)

2) After you have clicked on "salve" do you get a confirmation message? I didn't see a screenshot for that. Look at the online demo to see what I am referring to

Best,

 

[darren]

The "Item correctly updated" confirmation message? no, it just brings me back to the search page

also for the first question, I am not sure on the terminology of MySQL but this table is the the "Master record" that the other tables read from, so if I make a change here I have referential integrity for the other tables so the others will update, but there is nothing preventing a change made directly to the microchip table only the dependent tables

The primary key of the table is just an auto number field

However, your question gave me an idea, in dadabik I chose the microchip # as the unique field, as there cannot be more than one of the same microchip #

most importantly, the master/detail in Dadabik will not work unless you set this field as the unique field as the foreign key in other tables is not pointing to the auto-number field but to the microchip #.

Anyway, when I changed the unique field to the auto-number field, I could make changes to the microchip # so the problem disappeared, however, I gained the problem of not having my master/detail work anymore for this table.

In summation, Dadabik does not seem to allow edits to what is marked as the unique field, do you have any recommendations on how to allow edits of the microchip # and still have the master/detail work?

 

[eugenio]

I think there are more than one issues here; first of all, the fact that you are redirected to the search page is happening because you are changing the primary key value, this is a documented limitation:

https://dadabik.com/index.php?function=show_documentation#bugs

"if, while editing a record, you change the value of the primary key, DaDaBIK is not able to show you the updated record after saving."

This probably also explains the record remaining locked.

In general, I am not sure if I understood your schema: why you need to change the primary key value of that table? Do you have a table composed by just one field? If yes, probably you don't need the table in the first place.

Best,

 

[darren]

The Microchip # is not the table's primary key, it is the field I set in Dadabik to be the unique field and the reason I did this is because the Master/Detail form will not work unless the unique field which I set in the pages tab of the admin page is the same field in the form configurator where I set the master/detail.

Also yes maybe I could merge the whole table into another and have the same result but this would not solve the issue as I would then set the microchip # in the other table as the unique field (not the primary key) and still be unable to edit it and not have the master/detail work or be unable to edit it and have master/detail work.

If a limitation of Dadabik is setting the unique field in a table makes the field uneditable through Dadabik, that is fine, I will come up with a solution, I just did not know if something else was wrong or if I was missing something.

 

[eugenio]

Hello,
I am still not sure if I got your schema, but I'll give you some hints that might help anyway:

- normally you add a "details" table to a master/details by using a field that is foreign key in the "details" table and primary key in the "master" table, if you cannot do that, it might be that the schema is not well designed; what is the primary key of microchips?

- yes, editing the primary key value / unique key value of a table takes you out of the editing form, for the limitation I pointed out. The thing is that normally you don't have to edit the primary key / unique field value; also, very often the primary key / unique field value is an autoincrement value that you don't even want to see in your form.

- I think you should (but again, not sure if I got your db schema) add an autoincrement PK field to microchip (id_microchip) and link cats using that id_microchip. Cats will have a select_single id_microchip (foreign key) that, instead of displaying the id value, can display whatever you want in the microchips table (e.g. your microchip # value)

Best,

 

[darren]

- the primary key of my microchips table is an auto number field which I have no intention of editing directly

- the other tables are "details" of the master table as you described with foreign keys in them connecting to the master table

- The issue I am having is that if I set the unique field in Dadabik to the auto_number field, when I select the master table and want to see the details from other tables, it will not work as Dadabik will try to search for the record on the details table based on the field I set as the "Unique field" not on the actual field i chose in the page configurator as the master/details field.

How I got around this limitation was to set the microchip # as the unique field even though it is not the primary key, however, the issue I am facing is now I am unable to change the microchip # directly.

- your suggestion of using the auto increment as the foreign key is an interesting one, I will consider this as it will solve the issue I am facing.

 

[eugenio]

Hello,
just want to comment on this part:

The issue I am having is that if I set the unique field in Dadabik to the auto_number field, when I select the master table and want to see the details from other tables, it will not work as Dadabik will try to search for the record on the details table based on the field I set as the "Unique field" not on the actual field i chose in the page configurator as the master/details field.

In the form configurator you cannot choose that field, you only choose the field related to the details table, not the field of the master table. So you are right, is always the unique field on the master side. That's why using that field as a foreign key for your cats table would probably solve the issue.

Best,