Page 1 of 1
Missing foreign key on subform record
Posted: Fri Mar 01, 2024 10:28 am
by Costa
Good morning everyone.
I have a problem with a subform because the two files of the form and the subform are regularly transcribed to the database but the foreign key field does not show the ID of the form record but rather the word "autoid".
If I manually modify this field by inserting the relevant form record number, I am shown the form and subform with all the data.
The nubuilder log reports no errors. I'm working with Maria Db 10 and PHP 7.4.
Thanks for any help you can give me.
Costa
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 12:07 pm
by kev1n
Hi,
Could you show a screenshot of your subform settings and the db schema of the subform table?
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 12:40 pm
by Costa
Hi kev1n,
First of all thanks for your replay.
Here the Db subforn table structure:
Code: Select all
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Creato il: Mar 01, 2024 alle 17:03
-- Versione del server: 10.5.8-MariaDB-log
-- Versione PHP: 7.4.30
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `chico`
--
-- --------------------------------------------------------
--
-- Struttura della tabella `dettaglio`
--
CREATE TABLE `dettaglio` (
`iddettaglio` int(10) NOT NULL,
`det_progetto` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
`det_centro_di_costo` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
`det_lavorazione` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
`det_materiale` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
`det_quantita` decimal(10,2) NOT NULL,
`det_unita_misura` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
`det_prezzo` decimal(10,2) NOT NULL,
`det_totale` decimal(10,4) DEFAULT NULL,
`det_note` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`det_iddocumento` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dump dei dati per la tabella `dettaglio`
--
INSERT INTO `dettaglio` (`iddettaglio`, `det_progetto`, `det_centro_di_costo`, `det_lavorazione`, `det_materiale`, `det_quantita`, `det_unita_misura`, `det_prezzo`, `det_totale`, `det_note`, `det_iddocumento`) VALUES
(2, '1', '2', '2', '8', 3.00, '1', 12.00, 0.0000, NULL, '14'),
(3, '1', '2', '2', '8', 3.00, '1', 12.00, 0.0000, NULL, 'autoid');
--
-- Indici per le tabelle scaricate
--
--
-- Indici per le tabelle `dettaglio`
--
ALTER TABLE `dettaglio`
ADD PRIMARY KEY (`iddettaglio`);
--
-- AUTO_INCREMENT per le tabelle scaricate
--
--
-- AUTO_INCREMENT per la tabella `dettaglio`
--
ALTER TABLE `dettaglio`
MODIFY `iddettaglio` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
As you can see from the second record, the last field shows "autoid" while in the previous record I modified it by inserting the value "14" to see if the foreign key worked.
The settings of the subform object are in the attached screenshots.
Thanks for the help to understand...
Costa
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 12:46 pm
by kev1n
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 4:07 pm
by Costa
Yes, it seems like it is.
I hadn't previously tried to retype the subform data once the form had been saved but having done so I see that the two files are linked via foreign key. I noticed from the post you suggested the solution to insert the string
Code: Select all
if(nuIsNewRecord()){nuActionSave();
}
but I didn't understand where.
Thanks for your help
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 4:10 pm
by kev1n
This would go in the form's Custom Code (Edit Form)
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 4:18 pm
by Costa
I thought it should be put there but it doesn't work for me, same problem
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 4:20 pm
by kev1n
I haven't suggested/tested this workaround and suggest you use nuBuilder's UID primary key instead of an auto increment when dealing with subforms.
Re: Missing foreign key on subform record
Posted: Fri Mar 01, 2024 5:25 pm
by Costa
I reworked the database to use the nuBuilder uUID primary key and now everything seems to work.
Thanks for your time spent helping me