Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[lise]

How can I extract info from a field and have it populate a different custom field?

I have two custom user fields: "Fave Albums" and "Fave Artists".
I use them for the relational aspect of storing the info just once per artist or per album. That way, when I set Blue Rodeo as one of Jonnie's favourite artists (just by tagging a single Blue Rodeo track), all the Blue Rodeo tracks, including future ones I might add, will show up when I select "Fave Albums>Jonnie".

Note: inline images aren't working in preview so I have no clue if they will show up.
Edit: ok, the images didn't display inline.

But I prefer to the following custom field to deal with all the favourites (see image 2). So I want to turn the two fields above into calculated data fields, where they would get their content from my other field called "Favourites". If "Jonnie Albums" is checked in the Favourites field, then the calculated data in "Fave Albums" would list "Jonnie".

My solutions (that I can't get to work):

To populate the calculated fields I tried a few a few iterations of "if is equal", but the second condition always gets filtered by the first, such that if we both have the same album in our favourites list, it shows up in the first condition (Jonnie) but not the second (Lise). I don't know my way around it. I've tried multiple iterations of the following code based on the wiki. Here is just one that didn't work:

Code: [Select]

if(isequal([Favourites],Jonnie Albums,8),Jonnie albums,if(isequal([Favourites],Lise Albums,8),Lise Albums,unassigned))
Then I tried else in many different configurations but still couldn't get it to work:

Code: [Select]

ifelse((isequal([Favourites],Jonnie Albums,8), Jonnie albums, (isequal([Favourites],Lise Albums,8), Lise Albums)
If anyone can help with the code, that would be much appreciated. But if there is a simpler solution that doesn't require the separate calculated lists that would be even grander.

[JimH]

Hi lise!

[lise]

Hey Jim. I've been thinking about you. Was thinking about how you (and I) have it pretty good during Covid, living in the boonies (assuming you are still at the same place).  Also, I missed the special deadline to upgrade for the very first time ever! In how many years.... geez, we're talking Media Jukebox!

[JimH]

You've got mail, lise.  I'm even further out.  Nobody.  It's great.  Still waiting for you to find me a spot next to the marina so I can finish up rocking and watching people park their boats.  How's Jonny?

[lise]

Aw, you are so sweet. Thank you. I was just mentioning it because it was another reason I was thinking of you and the whole crew.
Jonnie is good. We are totally out of Toronto now, though, and live exclusively in the country outside Peterborough. Sold the condo a while back. 100% country bumpkins now. We have our cinema in the basement and our indoor hydroponic garden upstairs. Movies and Food. Is there anything else? (aside from tunes, of course!). I hope your daughters are well.

[wer]

...I tried a few a few iterations of "if is equal", but the second condition always gets filtered by the first...

lise,

I actually couldn't follow your narration of what you were trying to do with the fields, but perhaps that's not important, as I do understand your code. 

Since you got as far as you did, you obviously know what you're doing and probably don't need anyone to write something for you. You're on the right track... Your problem is only one of logic, not of a code error.

You're not taking into account the case where both a AND b can be true; your code tests case a, and if that fails, it tests case b, and that's it. It never tests a&b.

Right now your code evaluates to: if A, else if B, else fail.

The expression language has an AND statement. Use that to test the a&b condition first, all in an IfElse.

So your test should look like: if (A&B) ..., else if A ... , else if B ..., else fail.

The AND case test must come first, because the first match will exit the expression, and either your case a or case b will be true in the AND case.

See what I mean?

[lise]

Ah, didn't catch the "and" functionality in the wiki. I kept trying to get an (if a or b) then this, otherwise if a then this, if b then this. That's not quite what was in my example code, but it was definitely in one of my many attempts.

Thanks for the reply wer. Much appreciated.

[wer]

No problem. Is that enough to sort you out? 

If you wanted the "simpler" i.e. more elegant solution you asked about at the end, I could take a look at that, but in that case I would have to actually understand what you're trying to do, so I guess you'd have to explain again in a different way and maybe give an example, because you lost me with the "if it's selected in the favorites list" bit...

[lise]

wer,

I modified my original post. Perhaps it is more clear what I need?

Also, I've been at it all day long and I just can't get the "and" to work. I don't dare post everything I've tried because it would be so long...
What I really need is a if contains, that way if it contains Jonnie then select Jonnie, and if it contains Lise then select Lise and that way both Lise and Jonnie get added when they are both checked and also if just one of them is checcked. But I can't seem to put that together.

[lise]

I've made it even more basic:

Here's the code I need (it comes from the two bolded lines below)
If [Favourites] contains Jonnie Albums, then write Jonnie (total should be 63)
If [Favourites] contains Lise Albums, then write Lise (total should be 105)


When I do these searches in with the Wizard, here are the results
Wizard: 

All Jonnie fave albums:  [Favourites]=[Jonnie Albums] = 63 total Jonnie
Jonnie, not lise:  [Favourites]=[Jonnie Albums] -[Favourites]=[Lise Albums] = 34 unique to Jonnie
both Jonnie and lise:  +[Favourites]=[Jonnie Albums],[Lise Albums] = 29 that we both love
All Lise fave albums:  [Favourites]=[Lise Albums] = 105 total Lise
Lise, not jonnie:  -[Favourites]=[Jonnie Albums] [Favourites]=[Lise Albums] = 76 unique to Lise

[wer]

Try this:

Code: [Select]

IfElse(And(IsEqual([Favourites],Jonnie Albums,8),IsEqual([Favourites],Lise Albums,8)),Both,IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,IsEqual([Favourites],Lise Albums,8),Lise Only)
I still don't know what you mean by "select Lise from the acceptable values above" because an expression cannot "select" anything. It returns values, and it will return whatever value you dictate.  If you mean that you want it to actually extract the text from the list item of [Favourites] that can be done, but I think it's needlessly complicated because [Favourites] has restricted and known values.  So I think the expression above does what you want.

I don't know where you went wrong with your AND since you gave no examples, but it should be clear from the above that since AND tests values it requires a complete test expression (which will itself evaluate to a value) in each clause.  I've seen other people get confused with it though.

I deliberately left the output strings different than your example, to make the code clear. Change the strings "Both", "Jonnie Only", and "Lise Only" to whatever you want it to return in those cases.

[wer]

I see you posted again while I was posting.  The code I gave you above does what you were originally intending, if I understood enough.

Here's the code I need (it comes from the two bolded lines below)
If [Favourites] contains Jonnie Albums, then write Jonnie (total should be 63)
If [Favourites] contains Lise Albums, then write Lise (total should be 105)

That is how my expression will count.  The count of Jonnie + the count of Lise will be greater than the total favourites, because the unions (Lise&Jonnie) are counted for both.

[wer]

So just to expand on this, for anyone else who might be interested in IfElse() or And()... Here's the expression formatted for easier reading:

Code: [Select]

IfElse(
And(
IsEqual([Favourites],Jonnie Albums,8),
IsEqual([Favourites],Lise Albums,8)),
Both,
IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,
IsEqual([Favourites],Lise Albums,8),Lise Only)

The main IfElse contains 3 tests: an AND, and two IsEqual tests.

The AND itself compares two IsEqual tests, and if they are both true, outputs the text "Both".

If the AND test is not true, the IfElse moves on to the next test, IsEqual.

If the first IsEqual is true (the string in [Favourites] contains "Jonnie Albums") then it outputs "Jonnie Only" and otherwise, we go to the next test.

The last test is the same as the previous, but for Lise.

If none of the tests evaluates as true, the IfElse expression returns null (no output at all).

There is more than one way to evaluate for this set of conditions (it could be done with an IfCase statement or a Regex for example) but IfElse is the closest to what the OP originally understood, and is probably the easiest to understand.

[lise]

Thanks for the explanation of how the code works.  I appreciate it.

Code: [Select]

IfElse(And(IsEqual([Favourites],Jonnie Albums,8),IsEqual([Favourites],Lise Albums,8)),Both,IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,IsEqual([Favourites],Lise Albums,8),Lise Only)

I deliberately left the output strings different than your example, to make the code clear. Change the strings "Both", "Jonnie Only", and "Lise Only" to whatever you want it to return in those cases.

That's the issue. I want the output to be just Lise or Jonnie. I don't want a "both".  It is precisely turning that "both" into a "Jonnie" or "Lise" that is giving me headaches.

Code: [Select]

I still don't know what you mean by "select Lise from the acceptable values above" because an expression cannot "select" anything. It returns values, and it will return whatever value you dictate.
I meant "return" one of those two values, either Lise or Jonnie.

Basically, if "Jonnie Album" is checked in [Favourites] - output Jonnie.  If "Lise Album" is checked, output Lise.
Nothing else.

But boy, putting that into code...

I don't think it is possible with if/then, or maybe even with else. I spent most of this afternoon trying it with ListContains instead, convinced that a "contains" would work better because it wouldn't matter if both are checked, but no luck so far.

[lise]

Code: [Select]

IfElse(And(IsEqual([Favourites],Jonnie Albums,8),IsEqual([Favourites],Lise Albums,8)),[b]BOTH[/b],IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,IsEqual([Favourites],Lise Albums,8),Lise Only)


Where you have "Both", is there a way to output to both Jonnie and to Lise instead of "Both"?

[wer]

Just replace the word both with whatever you want it to say.

[lise]

Yeah, I know I can replace it with any word I want. I guess what I was asking is if I could replace it with two strings instead of one. So instead of "Both", it could be both "Lise" and "Jonnie". But I doubt that is possible, at least it wouldn't be easy.

I appreciate the time you took to help with the code. Unfortunately, it gives unexpected results.  I'll try and figure it out. I don't want to take up more of your time. You've been generous enough as it is.

Code: [Select]

IfElse(And(IsEqual([Favourites],Jonnie Albums,8),IsEqual([Favourites],Lise Albums,8)),Both,IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,IsEqual([Favourites],Lise Albums,8),Lise Only)
Result:
Expression Column
 All Expressions (4)
 0,Both,0,Jonnie Only,0,Lise Only)
 0,Both,1,Jonnie Only,0,Lise Only)
 1,Both,0,Jonnie Only,1,Lise Only)
 1,Both,1,Jonnie Only,1,Lise Only)

And when I insert the code you explained:

Code: [Select]

IfElse(
And(
IsEqual([Favourites],Jonnie Albums,8),
IsEqual([Favourites],Lise Albums,8)),
Both,
IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,
IsEqual([Favourites],Lise Albums,8),Lise Only)

The Results are:
Expression Colum
 All Expression (4)
 0,...
 0,...
 1,...
 1,...

[wer]

The code formatted for easy reading isn't tested for execution. Don't use it. Use the first one.

The first one works, I've tested it.  It's possible you made a cut and paste error, or it's possible your fields aren't setup as described.

Please show me a screenshot of the file list with all the involved fields visible, so I can see what you actually have. 

The code was tested against a database that had a list-type field called Favourites, that contained a combination of Jonnie Albums;Lise Albums

You refer to an expression column in your last post.  Please show me a screenshot of how you got that output, because the code that I gave you cannot generate an output of:

Expression Column
 All Expressions (4)
 0,Both,0,Jonnie Only,0,Lise Only)
 0,Both,1,Jonnie Only,0,Lise Only)
 1,Both,0,Jonnie Only,1,Lise Only)
 1,Both,1,Jonnie Only,1,Lise Only)

[wer]

I guess what I was asking is if I could replace it with two strings instead of one. So instead of "Both", it could be both "Lise" and "Jonnie". But I doubt that is possible, at least it wouldn't be easy.

Maybe the way you are using the terminology is confusing me.

The test case for the IfElse can output one string. Not any other number of strings, just one.  Two strings concatenated together are just one string. That string could be any of the following:
Both
Lise and Jonnie
"Lise and Jonnie"
Lise;Jonnie

You can however force MC to interpret that one string as another data type. This is called data type casting. You can cast a string as a list by appending

Code: [Select]

&datatype=[list]

to the string.  So you could have MC output "Lise;Jonnie" as a string, or you could cast that string as a list:

Code: [Select]

IfElse(And(IsEqual([Favourites],Jonnie Albums,8),IsEqual([Favourites],Lise Albums,8)),Lise;Jonnie&datatype=[list],IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,IsEqual([Favourites],Lise Albums,8),Lise Only)
In which case, MC outputs one list Lise;Jonnie instead of one string. The list has two elements, Lise and Jonnie.
I'm not sure how you would make use of that.

However, none of that explains how you got that erroneous output. Maybe something is missing in what you're telling me.

[wer]

Oh... Are you actually using MC24?  The expression language was enhanced in MC26. I wonder if And() is not present in MC24.

Click the magnifying glass in the expression editor dropdown, and look at the insert function menu.  Check and see if your MC has all the functions we are using.

[lise]

That's it. I'm running v24 on that machine.
I'll upgrade now to 27. I have to anyway as I have v27 everywhere else in the house.

[wer]

Perhaps we just added And() in 26; I remember talking about it, but I don't actually see it in the release notes.

If your MC is missing And() and nothing else, the expression can be rewritten without And() by using Math() instead, as follows:

Code: [Select]

IfElse(Math(IsEqual([Favourites],Jonnie Albums,8) & IsEqual([Favourites],Lise Albums,8)),Both,IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,IsEqual([Favourites],Lise Albums,8),Lise Only)

[lise]

I installed v27 and your code results are perfect!  (well, aside from the "both" that I don't want, but at least now I can see the results). Thanks!

[wer]

See my previous post from 11:01:35 pm about that.

[lise]

Genius! 

Code: [Select]

IfElse(And(IsEqual([Favourites],Jonnie Albums,8),IsEqual([Favourites],Lise Albums,8)),Lise;Jonnie&datatype=[list],IsEqual([Favourites],Jonnie Albums,8),Jonnie Only,IsEqual([Favourites],Lise Albums,8),Lise Only)

I just replaced the Jonnie Only and Lise Only for Lise and Jonnie and now my results are perfect: Just Jonnie and Lise (and unassigned) in my list, and when I clilck on either they show the correct numbers.

I *never* would have found that on my own. Heck, I was given'er all day with ListContains and ListCombines and anything else I could throw at it.

Thank you *so* much for your patience and your generosity.

[wer]

Congratulations!  Glad to help.