Grid subform can only add one record at a time

[Lala]

Hi
I have a Browse/Edit form called Household and a subform called Household Members. Primary key of Household is household_id which is joined to subform field household_id. The primary key of household members form is member_id.

I can open a household from the browse screen and it will show household members in a grid in the subform. So far, so good.
BUT
if I want to add more than one household member to the subform, it will only save the first record. Subsequent records are trying to use the same value for the member_id so the record isn't saved and the debug gives the message: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '675f3f78' for key 'PRIMARY' Why don't the new rows create new records?

Any help gratefully received!

[steven]

Hi Lala,

Can you show some screen shots, or even better, upload a copy of your database and we can have a look.

Steven

[Lala]

I'm attaching a backup - I don't know if that's helpful or not. If not, speaking very slowly and clearly as though to a total dipstick, please let me know how to attach a copy of my database

12-16-2024_000444_675f7cac880f2_nuBuilder_backup.sql.gzip

[steven]

Hi Lala,

Thanks for sharing your database.

Firstly your app seems to try to call a function that doesn't exist...

no_function.png

The only other thing is that all Primary Keys and Foriegn Keys need to be 25 characters long.

Otherwise you will get an error because 2 Primary Keys end up being the same when you chop them off at eight characters.

pk.png

The easy way to change them is running this SQL...

Code: Select all

ALTER TABLE `household` CHANGE `household_id` `household_id` VARCHAR(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL;
ALTER TABLE `household_members` CHANGE `household_id` `household_id` VARCHAR(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL;
ALTER TABLE `household_members` CHANGE `member_id` `member_id` VARCHAR(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL;

Steven

[Lala]

Thank you so much - I think the JS function was one of my desperate attempts to sort the matter which I forgot to remove. Your help with the id length is invaluable - I'll go and do it now and pray that it works!!

[kev1n]

Hi Lala,

I just wanted to check if you were able to test Steven's suggestions and if it's working now. Thanks so much for your feedback!