Incompatible Types for Comparison. Text, Number.

[dcastillo1]

Hello!

I have multiple multi-choice Dataverse columns.

I want to assign each choice a number. For example:

'Sales Channels' - Multi-choice column in a form

Amazon = 125
Shopify = 150

'Payment Processors' - Multi-choice column in a form

PayPal = 100
Stripe = 125

'Banks' - Multi-choice column in a form

Wells Fargo = 100

Chase = 75

Let's say Amazon, Shopify, WellsFargo and only PayPal are selected, I want them to be totaled up and saved to an Invoice column after I hit the save button.

So the Invoice column would read Invoice: $375

Here is my OnStart code I am using so far:

ClearCollect(SalesChannelsToInt,
{Choice: "Amazon", Value: 100, Id: GUID()},
{Choice: "eBay", Value: 125, Id: GUID()},
{Choice: "Shopify", Value: 150, Id: GUID()}
);
ClearCollect(PaymentProcessorsToInt,
{Choice: "PayPal", Value: 80, Id: GUID()},
{Choice: "Stripe", Value: 100, Id: GUID()},
{Choice: "Square", Value: 75, Id: GUID()}
);

Here is my Onselect Code for my Save button:

SubmitForm(Form1);

// Clear the collection "SelectedSalesChannelsInt" to prepare for fresh data.
Clear(SelectedSalesChannelsInt);

// For each item in the last submitted form's 'Sales Channels' field...
ForAll(Form1.LastSubmit.'Sales Channels'.Value,

// ... look up the associated value in the SalesChannelsToInt collection and add it to the SelectedSalesChannelsInt collection.
Collect(SelectedSalesChannelsInt, LookUp(SalesChannelsToInt, Choice = Value).Value)
);

// Clear the collection "SelectedPaymentProcessorsInt" to prepare for fresh data.
Clear(SelectedPaymentProcessorsInt);

// For each item in the last submitted form's 'Payment Processors' field...
ForAll(Form1.LastSubmit.'Payment Processors'.Value,

// ... look up the associated value in the PaymentProcessorsToInt collection and add it to the SelectedPaymentProcessorsInt collection.
Collect(SelectedPaymentProcessorsInt, LookUp(PaymentProcessorsToInt, Choice = Value).Value)
);

// Clear the collection "SelectedBanksInt" to prepare for fresh data.
Clear(SelectedBanksInt);

// For each item in the last submitted form's 'Banks' field...
ForAll(Form1.LastSubmit.Banks.Value,

// ... look up the associated value in the BanksToInt collection and add it to the SelectedBanksInt collection.
Collect(SelectedBanksInt, LookUp(BanksToInt, Choice = Value).Value)
);

// Sum up all the values
Set(TotalInvoiceValue, Sum(SelectedSalesChannelsInt, Value) + Sum(SelectedPaymentProcessorsInt, Value) + Sum(SelectedBanksInt, Value));

// Patch the result to the Invoice column
Patch(Companies, Form1.LastSubmit, { Invoice: TotalInvoiceValue })

It's currently patching a "0" to my invoice column and I have the yellow! error on the OnSelect property of my Save button.

Issue

Incompatible types for comparison. These types can't be compared. Text, Number.
We can't evaluate your formula because the values being compared in the formula aren't the same type.

Looks like these 3 chunks of the code are the culprit:

Collect(SelectedPaymentProcessorsInt, LookUp(PaymentProcessorsToInt, Choice = Value).Value)
Collect(SelectedSalesChannelsInt, LookUp(SalesChannelsToInt, Choice = Value).Value)
Collect(SelectedBanksInt, LookUp(BanksToInt, Choice = Value).Value)

Any suggestions would be lovely!

Thanks

[TheRobRush]

find the portion of the code that has the mismatch and surround one half of the comparison in Value( ) which turns a string into a value. Or in your original choice column collectiosn (assuming this is a single choice column so you only ever get one result like

Collect(SelectedSalesChannelsInt, Value(LookUp(SalesChannelsToInt, Choice = Value).Value))

[LaurensM]

Hi @dcastillo1,

I think the issue may be caused due to the nested functions with implicit record scope (ForAll & LookUp). Below you will find the adjustment made to the Banks ForAll - similar adjustments will need to be made to the other ForAll loops:

//See changes related to Main
ForAll(
    Form1.LastSubmit.Banks.Value As Main,
    Collect(SelectedBanksInt, LookUp(BanksToInt, Choice = Main.Value).Value)
);

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[TheRobRush]

Yah like @LaurensM mentioned there is a lot going on in the formula. Makes it a little difficult to read. You might try this is Laurens suggestion does not help you. Make the submit button do this, and only this.

SubmitForm(Form1)

then go to the form and find its OnSuccess property and set it to

Clear(SelectedSalesChannelsInt);
Clear(SelectedPaymentProcessorsInt);
Clear(SelectedBanksInt);

// For each item in the last submitted form's 'Sales Channels' field...
Collect(SelectedSalesChannelsInt, 

		ForAll(Form1.LastSubmit.'Sales Channels'.Value As firstRound,
			Value(LookUp(SalesChannelsToInt, Choice = firstRound.Value).Value)));

Collect(SelectedPaymentProcessorsInt, 

		ForAll(Form1.LastSubmit.'Payment Processors'.Value As secondRound,
			Value(LookUp(PaymentProcessorsToInt, Choice = secondRound.Value).Value)));

Collect(SelectedBanksInt, 

		ForAll(Form1.LastSubmit.Banks.Value As thirdRound,
			Value(LookUp(BanksToInt, Choice = thirdRound.Value).Value)));  

// Sum up all the values
Set(TotalInvoiceValue, Sum(SelectedSalesChannelsInt, Value) + Sum(SelectedPaymentProcessorsInt, Value) + Sum(SelectedBanksInt, Value));

// Patch the result to the Invoice column
Patch(Companies, { ID: Form1.LastSubmit.ID, Invoice: TotalInvoiceValue })

IF I am reading your original formula right that should work

[dcastillo1]

Thank you both for your suggestions and input. 

@LaurensM Can you explain the "as Main" part of that code and the same with your "as first, as second, and as third round" @TheRobRush ?

[dcastillo1]

This is the error I am receiving now with the updated code @TheRobRush 

[TheRobRush]

Ok so what column types are 

'Sales Channels'

'Payment Processors'

Banks

are these choice columns? and you want to sum up every choice selected in last submit? if so let's try this

Clear(SelectedSalesChannelsInt);
Clear(SelectedPaymentProcessorsInt);
Clear(SelectedBanksInt);

// For each item in the last submitted form's 'Sales Channels' field...
Collect(SelectedSalesChannelsInt, 

		ForAll(Form1.LastSubmit.'Sales Channels' As firstRound,
			Value(LookUp(SalesChannelsToInt, Choice = firstRound.Value).Value)));

Collect(SelectedPaymentProcessorsInt, 

		ForAll(Form1.LastSubmit.'Payment Processors' As secondRound,
			Value(LookUp(PaymentProcessorsToInt, Choice = secondRound.Value).Value)));

Collect(SelectedBanksInt, 

		ForAll(Form1.LastSubmit.Banks As thirdRound,
			Value(LookUp(BanksToInt, Choice = thirdRound.Value).Value)));  

// Sum up all the values
Set(TotalInvoiceValue, Sum(SelectedSalesChannelsInt, Value) + Sum(SelectedPaymentProcessorsInt, Value) + Sum(SelectedBanksInt, Value));

// Patch the result to the Invoice column
Patch(Companies, { ID: Form1.LastSubmit.ID, Invoice: TotalInvoiceValue })

this is assuming that 

Choice = thirdRound.Value

the column named choice in your lookup datasource is a Number or Text Column and the rows of this datasource contain each of the possible results picked in the choice column from the form. (if you get a mismatch here and it says Valeu cannot be Compared to text or something you may need to slightly alter the formula to 

Clear(SelectedSalesChannelsInt);
Clear(SelectedPaymentProcessorsInt);
Clear(SelectedBanksInt);

// For each item in the last submitted form's 'Sales Channels' field...
Collect(SelectedSalesChannelsInt, 

		ForAll(Form1.LastSubmit.'Sales Channels' As firstRound,
			Value(LookUp(SalesChannelsToInt, Choice = Value(firstRound.Value)).Value)));

Collect(SelectedPaymentProcessorsInt, 

		ForAll(Form1.LastSubmit.'Payment Processors' As secondRound,
			Value(LookUp(PaymentProcessorsToInt, Choice = Value(secondRound.Value)).Value)));

Collect(SelectedBanksInt, 

		ForAll(Form1.LastSubmit.Banks As thirdRound,
			Value(LookUp(BanksToInt, Choice = Value(thirdRound.Value)).Value)));  

// Sum up all the values
Set(TotalInvoiceValue, Sum(SelectedSalesChannelsInt, Value) + Sum(SelectedPaymentProcessorsInt, Value) + Sum(SelectedBanksInt, Value));

// Patch the result to the Invoice column
Patch(Companies, { ID: Form1.LastSubmit.ID, Invoice: TotalInvoiceValue })