Save record in a Parent and child table (MSSQL) at the same time using Patch

[KatheBlaschke]

Hi 

I'm trying to insert new records to two tables VISITAS (parent) and VISITA_SERVICIOS (child), FK ID_VISITA in the second one at the same time:

I found a video which reflects my case 
https://www.youtube.com/watch?v=Qp458n1q2bo&t=86s

However, when I tried to save the record, an error message pops-up with the following:

Basically, I created three collections:
1- VISITACOLLECT: To save the data in the table VISITAS

2- SERVICIOLINEACOLLECT: To gather several rows to be uploaded to VISITA_SERVICIOS (just missing 2 columns the PK ID_VISITA_SERVICIO and the FK ID_VISITA)
3- VISITASERVICIOCOLLECT: To save the data in the table VISITA_SERVICIOS

Below SERVICIOLINEACOLLECT:

Collect
        (
           SERVICIOLINEACOLLECT;
                               {ID_SERVICIOSL: DropServicios_1.Selected.ID_SERVICIO;
                                ID_EMPLEADOSL: DropEmpleado_1.Selected.ID_EMPLEADO;
                                PRECIO_DE_SERVICIOSL: PrecioInput_1;
                                UNIDADSL: DropUnidades;
                                NOMBRE_DEL_SERVICIOSL: 
                                               DropServicios_1.Selected.NOMBRE_DE_SERVICIO;
                                DESCUENTOSL: DescuentoInput
                                }
                                     
            ) 

Below the final patch to upload both tables at the same time.

ClearCollect
            (
            VISITACOLLECT;
                        Patch(
                            '[dbo].[VISITAS]'
                            ;
                            // Defaults('[dbo].[VISITAS]');
                            {ID_VISITA:Blank()};
                            {
                                ID_MASCOTA: ComboMascota_1.Selected.ID_MASCOTA;
                                FECHA_DE_VISITA: Now();
                                ACUDIENTE: AcudienteInput_1.Text
                                
                            }
                            )
               )
;;
Collect(
    VISITASERVICIOCOLLECT;
                   ForAll(
                            SERVICIOLINEACOLLECT;   
                            Patch
                                (
                                '[dbo].[VISITA_SERVICIOS]'; 
                                {ID_VISITA_SERVICIO: Blank()};
                                {ID_VISITA: First(VISITACOLLECT).ID_VISITA;
                                ID_EMPLEADO:ID_EMPLEADOSL;
                                ID_SERVICIO:ID_SERVICIOSL;
                                UNIDAD: UNIDADSL;
                                DESCUENTO: DESCUENTOSL;
                                PRECIO_DE_SERVICIO:PRECIO_DE_SERVICIOSL}
                                )
                          )
        )

It only shows "ERROR" after triggering the button, I'm not missing a single column. So what is the ClientRequestID, which the error is referring to?

Thanks for your help 😀

[JustInTime07]

@KatheBlaschke so you are allowing NULL on your Primary and Foreign keys in your SQL tables? That seems strange to me. In normal structured databases, PK and FK are not nullable. It defeats the purpose of the relationship schema.

[KatheBlaschke]

@JustInTime07 Hi, Thanks for your answer.

No, all the key ones are Not Null
This is my table:

dbo].[VISITA_SERVICIOS]
(
	[ID_VISITA_SERVICIO] [int] IDENTITY(1,1) NOT NULL,
	[ID_VISITA] [int] NOT NULL,
	[ID_SERVICIO] [int] NOT NULL,
	[ID_EMPLEADO] [int] NOT NULL,
	[UNIDAD] [decimal](20, 2) NULL,
	[PRECIO_DE_SERVICIO] [decimal](20, 2) NULL,
	[DESCUENTO] [decimal](20, 2) NULL
) 

[KatheBlaschke]

I also have to say, the first part of the formula works.

I have new records in table VISITAS every time I run it.

The issue is in the second part formula

[KatheBlaschke · Accepted solution]

I've found the Error!

I removed the table VISITA_SERVICIOS and added it again to the app. (as a fresh start)

For the collection, I've just kept the NOT NULL records which are basically all the ID (type int) 

Having done this. BUM! it works 😊

Now, it seems the problem with the other three columns was the type... All they are numbers ( in SQL is Decimal (20,2).)

Regarding that, I've found another post about the same issue:
https://powerusers.microsoft.com/t5/Building-Power-Apps/Insert-and-show-decimal-number-SQL-Server/m-...

I just need to add 

Value(DataInput.Text)

Long history short... now the below formula works perfectly 😊

ClearCollect
            (
            VISITACOLLECT;
                        Patch(
                            '[dbo].[VISITAS]'
                            ;
                            // Defaults('[dbo].[VISITAS]');
                            {ID_VISITA:Blank()};
                            {
                                ID_MASCOTA: ComboMascota_1.Selected.ID_MASCOTA;
                                FECHA_DE_VISITA: Now();
                                ACUDIENTE: AcudienteInput_1.Text
                                
                            }
                                )
    )
;;
Collect(
    VISITASERVICIOCOLLECT;
    ForAll(
        SERVICIOLINEA;          Patch('[dbo].[VISITA_SERVICIOS]'; 
                                {ID_VISITA_SERVICIO: Blank()};
                                {ID_VISITA: First(VISITACOLLECT).ID_VISITA;
                                ID_EMPLEADO:ID_EMPLEADOSL;
                                ID_SERVICIO:ID_SERVICIOSL;
                                PRECIO_DE_SERVICIO: Value(PrecioInput_1.Text);
                                UNIDAD: Value(UnidadInput.Text);
                                DESCUENTO: Value(DescuentoInput.Text)
                                }
                                )
            )
        )

Thanks for reading, I hope it helps someone else!

[JustInTime07]

@KatheBlaschke  I'm glad you got it sorted out 🙂