I have a unique lookup problem that I am trying to solve. I have a “FormQ” that contains “FieldA” and “FieldB”. What I am trying to do is this: from within every document created with FormQ, I am trying to show every other document created using “FormQ” where a value in EITHER FieldA or FieldB matches a value in EITHER FieldA or FieldB on a other FormQs. To restate… if FieldA is equal to “cat” on the current doc, I’d like to pull back all of the docs that have “cat” in FieldA OR FieldB. And the same is true with FieldB.
The original customer requirement was only FieldA so this was not an issue… I simply made a single category embedded view. Is there a way that I can do this to keep it in one embedded view?
Subject: A unique lookup issue
Just make your first sorted/search columnFieldA:FieldB That way all your @dblookup operations find either.
If that’s not exactly to your needs, don’t be afraid to create an additional view that does precisely what’s needed. What the user sees and what happends in the background don’t have to be the same components just as long as the results are correct.
Collin
KC8TKA
Subject: RE: A unique lookup issue
Thanks so much Collin, but I’m a little confused. First, is there a way to pull this off in an embedded view? I thought the single category had to be an exact match.
As for db lookups… I suppose that I could do this using @dblookup and not pull back exact matches, right? I guess the downside of this is that I would have to make the results be static text but that wouldn’t be the end of the world.
Subject: RE: A unique lookup issue
If you use an embedded view where you’re showing a single category, that means matching a specific value. That value can come from more than one field. So you can put a multi-value item into the category column and let the single-category operation display that data.
Right now I’d suggest that you sit down with a pencil and paper (or Visio, but paper truely is better) and draw pictures with lines, showing what you’ve got and what you want to get out of it. It should all come together righ in fron to you.
Pictures are often the best and simplest teachers.
Collin
KC8TKA
Subject: RE: A unique lookup issue
Thanks Collin. I think there is some confusion here… the concept is fairly simple and I understand what I need to do. The problem that I’m having is that technically I’m not sure how to get there.
What I don’t understand about your scenario is that by saying “FieldA : FieldB” is that you seem to be using an “or” in the single category area of the embedded view. am I understanding you correctly? I was not aware that you could say “or” in the lookup.
Subject: RE: A unique lookup issue
Don’t think of it as binary logic.All I’m suggesting is that the two fields occupy the same column so that you can look up a value irregardless of which field stores the value. It’s a list concatenation.
Collin
Subject: RE: A unique lookup issue
Ok, thanks so much. I think that is exactly where my brain is messing this one up. I’m going to try it out right after lunch. If I am to understand you correctly, I’d be doing this concatenation both in the single cat field and in the first column of the view?
Subject: RE: A unique lookup issue
No. Only in the view.The single category selection must be single.
Subject: RE: A unique lookup issue
Ok, that’s what I am confused about. I understand looking up to multiple values in the view… how do I do this from the form? I need to see if a value in FieldA OR FieldB matches the concatenated value in column 1.
Subject: RE: A unique lookup issue
That is what categorization does for you. If either FieldA OR FieldB matches the value you want, the document will appear.
You appear to be a bit confused about concatenated values and categorization. If I had the value:
“A”:“B”
as the column value, I would have two categories, “A” and “B”, and the document would appear in both categories.
Subject: RE: A unique lookup issue
No, that’s the part that I do understand as I’ve utilized it in the past when looking up a single value. It’s how to say “A” or “B” in the single category area of the embeddied view element on the form that is thrwoing me for a loop. From your earlier post, you’re saying that I can say “A” or “B” in the form element and I will try that
Subject: A unique lookup issue
Sure, use the same categorized view you would have used for FieldA, but use a column formula of:
@Unique(@Trim(FieldA:FieldB))
instead of
FieldA
You don’t really even need the @Unique, but the @Trim is a good idea in case a field may be empty.
Subject: RE: A unique lookup issue
I’m assuming that you mean this for a Dblookup? The other component is that I’d need to do the same thing for fieldB - I need to search on that as well.
Subject: RE: A unique lookup issue
Use the formula I gave you and the single category embedded view. The value will be there as a single category, just the way you want, whether the document is in FieldA or FieldB.
Subject: RE: A unique lookup issue
Thanks Ben… I’m not at that client site right now but I will be this afternoon. So by that logic I’d also need to have the same formula in the first column of the view that I am pointing to in the embedded area?