cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MikeAnderson
Level: Powered On

Collect not saving to Azure SQL database

Hello,

I'm havining an issue using Collect to save a collection to my Azure SQL Database.  I have 2 Collect functions, one works saving a collection and one does not. I can't seem to identify why the second one is not working.  It does not throw an error and I'm looking for some help as I've run out of ideas or things to test.  Here is the layout of both:

 

Working Collect and Collection:

PowerApps Collect Code: 

Collect('[dbo].[Requests]', RequestCollection);

PowerApps Collection Code:

Collect(
    RequestCollection,
    {
        RequestID: varRequestID,
        Job: JobCombobox.Selected.Job,
        JobDescription: JobCombobox.Selected.Description,
        JobLabel: JobCombobox.Selected.Label,
        PhaseCode: PhaseCodeCombobox.Selected.Phase,
        PhaseDescription: PhaseCodeCombobox.Selected.Description,
        PhaseLabel: PhaseCodeCombobox.Selected.Label,
        ShipAddress1: JobCombobox.Selected.ShipAddress,
        ShipAddress2: JobCombobox.Selected.ShipAddress2,
        ShipCity: JobCombobox.Selected.ShipCity,
        ShipState: JobCombobox.Selected.ShipState,
        ShipZip:JobCombobox.Selected.ShipZip,
        ShipCountry: JobCombobox.Selected.ShipCountry,
        SpecialInstructions: SpecialInstructionsTextbox.Text,
        RequestedBy: varCurrentUser.FullName,
        RequestedByEmailAddress: varCurrentUser.Email,
        SubmittedDate: Now(),
        IsPending: true
    }
);

Database table schema for this collection:

	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RequestID] [char](25) NOT NULL,
	[Job] [varchar](10) NOT NULL,
	[JobDescription] [varchar](60) NOT NULL,
	[JobLabel] [varchar](70) NOT NULL,
	[PhaseCode] [varchar](20) NOT NULL,
	[PhaseDescription] [varchar](60) NOT NULL,
	[PhaseLabel] [varchar](80) NOT NULL,
	[ShipAddress1] [varchar](60) NULL,
	[ShipAddress2] [varchar](60) NULL,
	[ShipCity] [varchar](30) NULL,
	[ShipState] [varchar](4) NULL,
	[ShipZip] [varchar](12) NULL,
	[ShipCountry] [char](10) NULL,
	[SpecialInstructions] [varchar](max) NULL,
	[RequestedBy] [varchar](50) NOT NULL,
	[RequestedByEmailAddress] [varchar](60) NOT NULL,
	[SubmittedDate] [datetime] NOT NULL,
	[IsPending] [bit] NOT NULL,
	[FulfilledDate] [datetime] NULL,
	[FulfilledBy] [varchar](50) NULL,
	[FulfilledByEmailAddress] [varchar](60) NULL,
	[ModifiedDate] [datetime] NULL,

Not working Collect and Collection:

PowerApps Collect Code: 

Collect('[dbo].[EquipmentRequests]', EquipmentCollection);

PowerApps Collection Code: 

Collect(
    EquipmentCollection,
    {
        RequestID: varRequestID,
        EquipmentCategory: CategoryCombobox.Selected.Description,
        EquipmentSubcategory: SubcategoryCombobox.Selected.Description,
        EquipmentQuantity: Value(QuantityDropdown.SelectedText.Value),
        StartDate: StartDateCalendar.SelectedDate,
        EndDate: EndDateCalendar.SelectedDate,
        Days: DateDiff(StartDateCalendar.SelectedDate,EndDateCalendar.SelectedDate,Days),
        LineID: LineID
    }
);

Database table schema for this collection: 

	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[RequestID] [char](16) NOT NULL,
	[LineID] [tinyint] NOT NULL,
	[EquipmentCategory] [varchar](200) NOT NULL,
	[EquipmentSubcategory] [varchar](30) NOT NULL,
	[EquipmentQuantity] [smallint] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[Days] [int] NOT NULL,
	[RequestedBy] [varchar](50) NOT NULL,
	[RequestedByEmailAddress] [varchar](60) NOT NULL,
	[SubmittedDate] [datetime] NOT NULL,
	[ModifiedDate] [datetime] NULL,

The only difference between the RequestCollection that does save to the database and the EquipmentRequestCollection that does not save is that the first collection only contains 1 row, whereas the EquipmentReqeustCollection contains multiple rows.  However, based on my understanding of this https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearc... each record of a table should be added as a seperate record.  Here is a simple example of my EquipmentRequestCollection in test. The code itself is throwing no errors, so I'm not sure what else I can do to troubleshoot this.  Any and all suggestions welcome.  I've looked at using Errors() - https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-errors but it's not clear to me how to implement this.
12.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
MikeAnderson
Level: Powered On

Re: Collect not saving to Azure SQL database

Hi v-dia-msft,

 

Thank you for the suggestion, however the identity column is the primary key column as well, I just didn't show that in the script definition.  The full definintion includes this as well: 

ALTER TABLE [dbo].[EquipmentRequests] ADD  CONSTRAINT [PK_EquipmentRequests_ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)

It's not clear to me why I should remove the NOT NULL requirement.  This constraint is an additional precaution to ensure all required fields are enforced at the database side.  The other table with the NOT NULL constraints operates just fine.  Can you explain the "why" behind you think I should remove the constraints?


Also, why ForAll/Patch vs Collect? I'm just curious to understand the reasoning behind using ForAll/Patch vs Collect as Collect seems easier to use.  I'm new to PowerApps hence the question.

Finally, THANK YOU to everyone who offered suggestions.  I have found with your help what my issue was, though I wish PowerApps had better or more verbose error reporting capabilities.  Maybe it does and I just don't know how to properly implement it.

 

1. I had changed how the ID in the [RequestID] column was generated to ensure uniqueness.  In doing so, I forgot to increase the column with from char[16] to char[25].  I'm guessing a "string or binary would be truncated error" was happening but I couldn't see that.  Once I updated the [RequestID] column to char[25] 1 of 2 records was being inserted.

 

2. I also identified an issue with my LineID code, whereas I had change the variable from LineID to varLineID but had not updated all the references.  The missing row was because the LineID was null and the database required a numeric value. 

 

Everything is working now and I'm setting out to learn how to capture and show errors when using patch so that I can better resolve situations like this in the future.

 

And on a side note, I did try the ForAll/Patch code above and it worked the same. I'm not clear on the benefit or difference of using one over the other but I did test it and they both work.

 

I'm not sure who to give credit to as this was a group particiation that helped me resolve this.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Collect not saving to Azure SQL database

@MikeAnderson 

Are you missing some required fields?  

You have some fields with a NOT NULL Constraint on them and you are not supplying them in your record.

	[RequestedBy] [varchar](50) NOT NULL,
	[RequestedByEmailAddress] [varchar](60) NOT NULL,
	[SubmittedDate] [datetime] NOT NULL,

Perhaps supplying them in your record would resolve the issue for you.

 

I hope that this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
MikeAnderson
Level: Powered On

Re: Collect not saving to Azure SQL database

Thank you for catching a missing schema change.  I had modified the schema on the EquipmentRequests table this morning but forgot to persist the change.  The schema now looks like this but is still not saving the records.

	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[RequestID] [char](16) NOT NULL,
	[LineID] [tinyint] NOT NULL,
	[EquipmentCategory] [varchar](200) NOT NULL,
	[EquipmentSubcategory] [varchar](30) NOT NULL,
	[EquipmentQuantity] [smallint] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NOT NULL,
	[Days] [int] NOT NULL,
	[ModifiedDate] [datetime] NULL,

On an interesting note, PowerApps was warning me before if I was missing required fields, so interesting to me I missed those and it threw no warning, but thank you for catching that.  

 

Any other thoughts by chance?

Community Support Team
Community Support Team

Re: Collect not saving to Azure SQL database

Hi @MikeAnderson ,

Have you defined a proper PRIMARY KEY in your SQL Table ('[dbo].[EquipmentRequests]')?

 

Based on the SQL Table schema that you provided, I find that you do not define a PRIMARY KEY in your '[dbo].[EquipmentRequests]', is it true?

Please consider define a proper PRIMARY KEY in your '[dbo].[EquipmentRequests]' table, check if the issue is solved.

 

In addition, please consider remove the NOT NULL constrains from your '[dbo].[EquipmentRequests]' schema. In other words, please consider re-create a new '[dbo].[EquipmentRequests]' table based on the following SQL Syntax:

 

        [ID] [bigint] IDENTITY(1,1) PRIMARY KEY,
	[RequestID] [char](16),
	[LineID] [tinyint],
	[EquipmentCategory] [varchar](200),
	[EquipmentSubcategory] [varchar](30),
	[EquipmentQuantity] [smallint],
	[StartDate] [datetime],
	[EndDate] [datetime],
	[Days] [int],
	[RequestedBy] [varchar](50),
	[RequestedByEmailAddress] [varchar](60),
	[SubmittedDate] [datetime],
	[ModifiedDate] [datetime],

then within your app, re-create a new connection to the new created SQL Table, try your formula again, check if the issue is solved.

 

 

Also please consider use the combination of ForAll function and Patch function to achieve your needs. please take a try with the following formula:

 

ForAll(
         EquipmentCollection,
Patch(
'[dbo].[EquipmentRequests]',
Defaults('[dbo].[EquipmentRequests]'),
{
RequestID: EquipmentCollection[@RequestID],
EquipmentCategory: EquipmentCollection[@EquipmentCategory],
EquipmentSubcategory: EquipmentCollection[@EquipmentSubcategory],
EquipmentQuantity: EquipmentCollection[@EquipmentQuantity],
StartDate: EquipmentCollection[@StartDate],
EndDate: EquipmentCollection[@EndDate],
Days: EquipmentCollection[@Days],
LineID: EquipmentCollection[@LineID]
}
) )

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
MikeAnderson
Level: Powered On

Re: Collect not saving to Azure SQL database

Hi v-dia-msft,

 

Thank you for the suggestion, however the identity column is the primary key column as well, I just didn't show that in the script definition.  The full definintion includes this as well: 

ALTER TABLE [dbo].[EquipmentRequests] ADD  CONSTRAINT [PK_EquipmentRequests_ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)

It's not clear to me why I should remove the NOT NULL requirement.  This constraint is an additional precaution to ensure all required fields are enforced at the database side.  The other table with the NOT NULL constraints operates just fine.  Can you explain the "why" behind you think I should remove the constraints?


Also, why ForAll/Patch vs Collect? I'm just curious to understand the reasoning behind using ForAll/Patch vs Collect as Collect seems easier to use.  I'm new to PowerApps hence the question.

Finally, THANK YOU to everyone who offered suggestions.  I have found with your help what my issue was, though I wish PowerApps had better or more verbose error reporting capabilities.  Maybe it does and I just don't know how to properly implement it.

 

1. I had changed how the ID in the [RequestID] column was generated to ensure uniqueness.  In doing so, I forgot to increase the column with from char[16] to char[25].  I'm guessing a "string or binary would be truncated error" was happening but I couldn't see that.  Once I updated the [RequestID] column to char[25] 1 of 2 records was being inserted.

 

2. I also identified an issue with my LineID code, whereas I had change the variable from LineID to varLineID but had not updated all the references.  The missing row was because the LineID was null and the database required a numeric value. 

 

Everything is working now and I'm setting out to learn how to capture and show errors when using patch so that I can better resolve situations like this in the future.

 

And on a side note, I did try the ForAll/Patch code above and it worked the same. I'm not clear on the benefit or difference of using one over the other but I did test it and they both work.

 

I'm not sure who to give credit to as this was a group particiation that helped me resolve this.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 337 members 5,701 guests
Please welcome our newest community members: