Formula Text Issue

Question

Not sure what I'm doing wrong but the formula below isn't passing the numeric value I think it would.

To set the stage:
Variable [Channel43] = Broadband Back Office
Variable [Channel46] = Video Back Office

Formula:
If(ToText([IntakeID - Channel Summary])=ToText([Channel46]), "4",
If(ToText([IntakeID - Channel Summary])=ToText([Channel43]), "3",
If(ToText([IntakeID - Channel Summary])=List(";",[Channel43],[Channel46]), "2",
If(Contains(ToText([IntakeID - Channel Summary]),ToText([Channel43] )), "1",
If(Contains(ToText([IntakeID - Channel Summary]),ToText([Channel46] )), "1", "0")))))

Record in question - IntakeID - Channel Summary = Broadband Back Office;Video Back Office

I'm interpreting that, based on the formula, it should pass "2" but it's passing "1". I've tried using "ToText" in front of the "List" and it still passes "1". What in the holy heck am I doing wrong with the formula?

------------------------------
Michael Sargent
------------------------------

markshnier__you · Answer

Can you tell us an example of the contents of each of the fields being used here and what the result is and what result you were expecting?.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

michaelsargent · Answer

The IntakeID - Channel Summary is a summary of 26 potential channels being impacted by the project. For the formula above, only two channels are being factored, Broadband Back Office and Video Back Office. Each of those two channels equate to a variable field (listed above). I'm using the NBO number formula to then change a status field (example NBOFormula = 4 changes status to BO-V; 3 to BO-B, etc.)

What I can't figure out is why a record that has both values, in the right order, isn't displaying "2". Do you see any issue with that 3rd row of code?

------------------------------
Michael Sargent
------------------------------

markshnier__you · Answer

I would need to have example data to suggest a problem with the formula. For all I know the data may not be what you think it is and n fact the formula may be perfect.

I would need an example of specifically on the test record as to what value is in every field used in that formula.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

michaelsargent · Answer

Morning Mark,Okay, that formula is based entirely on 3 fields.&nbsp; There is the:

IntakeID - Channel Summary
Variable - [Channel43]
Variable - [Channel46]

For the particular record in question, here is how the IntakeID - Channel Summary field appears in an export to Excel:

IntakeID - Channel Summary

Broadband Back Office;Video Back Office

Here is variable Channel 43:Here is variable - Channel 46:I've spot checked spelling, I've validated the delimiter, and I've verified no extra spaces.&nbsp; I just wonder if I should use the actual text instead of variables and see if that resolves the issue?------------------------------Michael Sargent------------------------------

markshnier__you · Answer

Perhaps there is a trailing space after the text in ([IntakeID - Channel Summary])IF(Trim([IntakeID - Channel Summary])=[Channel46], "4",Trim([IntakeID - Channel Summary])=[Channel43], "3",Trim([IntakeID - Channel Summary])=List(";",[Channel43],[Channel46]), "2",Contains(Trim([IntakeID - Channel Summary]),[Channel43]), "1",Contains(Trim([IntakeID - Channel Summary]),[Channel46]), "1", "0")also when you said this

IntakeID - Channel Summary

Broadband Back Office;Video Back Office

I presume that the data is really thisBroadband Back Office;Video Back Office------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Formula Text Issue

Related Content

Rich-text formula button on dashboard

Missing Records/Data Issue

Quickbase App Issues

Merging 2 Fields into a Formula-Text Field for Reporting Purposes

Sorting Combined Text Field by Record ID#