Unanswered

Collection using local sql - data types

(0) Share

Report

Posted on by TML

185

I have an app that Collects data from a local Gateway SQL table into a Powerapps collection. When I try to add a new row to the collection, I get an error that the data types do not match.

I have narrowed it down to a date field. If I export the app and view the JSON Entities file, it shows me that when I collect from SQL, the date field has a data type of "lowercase d". However, when I try to Collect and add a new row to the collection, the data type is "uppercase D". I have tried many things... changing the datatype in SQL from datetime to: date, datetime2, and dateoffset. On the PowerApps side, I have tried using the date control's SelectedDate, using a string, using the DateValue of a string. Obviously the string won't work, but the other two both have a data type of "uppercase D".

When trying to update from Collection inside of PowerApps, this is the data type:

I cannot get anything to update the collection. What am I missing or not understanding?

Any help is greatly appreciated.

Categories:

Building Power Apps

All responses (6)

Answers (0)

Sort by

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @TML,

How do you create the collection within your app?

Further, how do you add a new row to the collection that you created? Could you please share a bit more about the formula within your app?

I have made a test on my side, and don't have the issue that you mentioned.

The error message told that there is a data type mismatch issue within the formula that you use to add a new row to the connection. The DateReporting column is a datetime type column, but the dteReportingDate.SelectedDate formula returns a Date value.

Please take a try to format the date value (dteReportingDate.SelectedDate) that you provided for the DateReporting column into a datetime value, and then check if the issue is solved.

Please take a try to modify your formula as below:

Collect(
Collection1,
{
CareTaker:"",
Comments:"",
DateReporting:DateTimeValue(Text(dteReportingDate.SelectedDate)),
FarmCode:"",
...

}
)

Note: The Collection1 represents the Collection that you created within your app, which collects data from your local Gateway SQL table.

Best regards,

Kris
TML 185 on at

Like
a
(0)

Report
Copy link

Link copied!
When the app's first screen opens, it reads in existing data from the SQL table. Below is the code:
ClearCollect(
DCIRecords,
SortByColumns(
Filter(
AddColumns(
'[dbo].[DCI]',"NeedsSync",false),
FarmCode=gFCode),
"DateReporting",
Descending));

Later in the app when it is determined that I need to add a row to the Collection, I use this code:
Collect(DCIRecords, { FCode: gFCode, DateReporting: DateValue(vSelectedDate), Room: vRoom, HighTemp: vHTemp, LowTemp: vLTemp, TempSetPoint: vTempSetPt, WaterMeterUsage: vWaterUsage, Comments: vComments, NeedsSync: gNeedsSync } );
I have also tried using dteReportingDate.Selected date and either way it give me the same outcome. I have also changed the data type in the SQL column and have tried DateTime, DateTime2, Date, and DateOffset. All have the same outcome and think their is a data type mismatch.
TML 185 on at

Like
a
(0)

Report
Copy link

Link copied!

Is there documentation that explains the letter codes for the different data types?
TML 185 on at

Like
a
(0)

Report
Copy link

Link copied!

I also tried just now using DateValue and the string date, and concatenated a time to it as well and still have the same results.
TML 185 on at

Like
a
(0)

Report
Copy link

Link copied!

Does this mean no one can answer the question or help me solve the problem?
joeyb1215 15 on at

Like
a
(1)

Report
Copy link

Link copied!

Don't know if you are still looking for an answer, but I just recently had this issue and solved it. I have a Gallery full of Jobs that change colors if they are updated with any information. So when a change is made to a Job, it collects the "Job Number" as JOBNUM and a "Work Type" as WRKTYPE in a collection. So I first tried the fill color to read If Lookup(Collection, JOBNUM =Thisitem.Job # & WRKTYPE=ThisItem.Work Type), change to Green. I was getting an Invalid Argument Error stating that Work Types were different data types(I tried Value() and Text() functions and was still unsuccessful). So then I tired collecting "Job Number&Work Type" as JOBNUM, and when I referenced IF JOBNUM=ThisItem.Job Number&Work Type it worked. Hope that helps.