Having Trouble hardcoding values to collection

[stephenJames]

Hello,

         I have an 'If' statement that checks for a value in a collection and if true creates a new collection with some hardcoded values, if false it creates the same collection but with values pulled from a filter collection. 

I seem to be getting the red squiggly line of death, but as usual with these errors they are vague to me and dont point me to what the issue is. 

If(
   IsBlank(
           Filter(
                  col1,JobTitle = "ThisJobtitle"
           )
   ),
   ClearCollect(
                col2,
                {
                FirstName:"FirstName",
                LastName:"LastName",
                JobTitle:"AnotherJobTitle",
                WorkPhone:"555-555-5555",
                Email:"some@email.com"
                }
   ),
   ClearCollect(
                col2,
                Filter(
                       col1,
                       JobTitle = "ThisJobTile"
                )
   )
);

From my understanding, the first section is filtering through col1 to see if there is a value of "ThisJobTitle" in the column "JobTitle". If there is not a record that can be found it returns a true value, and starts the second section which collects hardcoded data into col2. If statement returns false then the thrid section is run to collect data from col1 into col2 based on the filter of the record containing the desired "JobTitle".

The red Squiggly seems to start on the first ClearCollect and has a darker red color on the "col2" part of the second and third sections. When i hover over it i get the message " Incompatible type. The item you're trying to put into a collection has a type that's not compatible with the collection". 

I'm not really understanding what is not compatible about the hardcoded text i am adding to the newly created collection. 

Im not understanding how the hardcoded "Text" data is incompatible with the collection. 

[timl]

Hi @stephenJames 

The first thing I would note here is that the initial call to IsBlank should be IsEmpty. IsEmpty is the function that tests for an empty table.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-isblank-isempty

The errors that you see may be a manifestation of this initial problem. Therefore, I would change the condition to use IsEmpty and review the remaining errors again following this change.

[stephenJames]

Hi @timl ,

Thanks for the catch on the IsBlank to IsEmpty. 

The errors still seem to be there, however i think i have narrowed it down a little more. I remove everything within the {} and started adding each hardcoded item one by one until i got a red squiggly. 

It seems as though when i get to "Division" (which isnt in my example above but is part of my overall hardcoded items), this is when i get a red squiggly. Now the only difference between this item vs the others is that in the "col1" this item is a record with a value. Could this be the reason why i am getting an error, because it is expecting a record value instead of the hardcoded text i have provided. If so how do i update my hardcoded text to insert a record value instead? 

[timl · Accepted solution]

Hi @stephenJames 

Yes, that would explain the error that you're seeing. The syntax you would use to define a record value would look like this:

If(
   IsEmpty(
           Filter(
                  col1,JobTitle = "ThisJobtitle"
           )
   ),
   ClearCollect(
                col2,
                {
                FirstName:"FirstName",
                LastName:"LastName",
                JobTitle:"AnotherJobTitle",
                WorkPhone:"555-555-5555",
                Email:"some@email.com",
                Division:{YourFieldName:"YourDivisionValue"}
                }
   ),
   ClearCollect(
                col2,
                Filter(
                       col1,
                       JobTitle = "ThisJobTile"
                )
   )
);

Just in case Division is a table value (rather than a record value), the syntax would look like this:

If(
   IsEmpty(
           Filter(
                  col1,JobTitle = "ThisJobtitle"
           )
   ),
   ClearCollect(
                col2,
                {
                FirstName:"FirstName",
                LastName:"LastName",
                JobTitle:"AnotherJobTitle",
                WorkPhone:"555-555-5555",
                Email:"some@email.com",
                Division:Table({YourFieldName:"YourDivisionValue"})
                }
   ),
   ClearCollect(
                col2,
                Filter(
                       col1,
                       JobTitle = "ThisJobTile"
                )
   )
);

[stephenJames]

That worked perfectly @timl thank you.

Just for some clarification, you mentioned if Division was a table value vs a record value. How do you know?

I thought collections was just a fancy name for Table, with each line being a Record within the collection. 

[timl]

Hi @stephenJames 

Good question. Table is a data type, in the same way that numbers, text, and dates are data types. A collection defines a variable that is of data type table.

You can tell whether a field is table or a record through the IntelliSense in the designer.

For instance, if I add a table field called DivisionTable, the formula bar indicates the data type Table. Likewise, if I add a record field called DivisionRecord, the data type shows up as record.

[stephenJames]

Thanks for the information @timl .

So most of my issue is now resolved with a couple of minor issues. 

So with my hardcoded data i have one column ImageURI that i cant really hardcode as it is a lot of data to paste into 

{ImageURI:}

 This data is located in another collection. Is there a way to do a lookup or search in that other collection and pull it to this collection. 

If so how would i go about doing that?

[timl]

Hi @stephenJames 

You can lookup the value that you want to pass by calling the LookUp function. The code would look along the lines of this:

{ImageURI:LookUp(SourceCollection, KeyField="KeyValue").ImageURIField}