Scripting

Does anybody know if you can search a UDT based on a field that is NOT the key field? If so, how? TIA!

Doesn't always apply, depending on the situation, but if you can create a custom lookup and the UDF to the mix, then it becomes one of the fields you can search on....

Samantha, It depends on how you are using the UDT. Did you create a UDF and link it to a UDT for verification? If so, then the magnifying glass icon will give you the content of the UDT and you can search the other columns in various ways. Are you thinking about a vb script? You can open the UDT as an ODBC RecordSet. and use a Select statement to find content in other columns. You can also use the GetResultSets ""where"" clause.

I'm sorry! I should have been way more specific... I have a script that's looking to the UDT for a non-key field value, if it finds the value in that field, I want it to return the key field. Something like this...oMyUDT.Find(mynonkeyfield)

Not that I am aware of. We've done some creative concatenation with key fields Div^Custno^SomeOtherData^... then used a browse filter to narrow results, but otherwise it is a MoveFirst / MoveNext through all the records until you find what you seek.

Like Kevin I usually setup UDT to also do creative concatenation scheme so it effectively has multiple columns instead of a single column and in the order you want. Extending Kevin's example you could also add separate non-key fields for Div, CustNo, etc. You could also have an M/D type add an index to the dictionary for you but then that may have to be maintained. The essential issue is UDTs have only 1 key/index (the primary key) and it is based on a single column. You don't get to create your own separate indexes during UDT creation process. If it were a standard table and it has more than 1 index and it's useful, then you can do SetBrowseIndex() to change your search to use that index, then SetBrowseFilter() if possible, then the Find() to get a fast search result. In your case this is how you might proceed using linear top to bottom searching but let's add SetBrowseFilter to make it go faster (also in the scripting class examples). Let's say you have UDT of famous mice (maybe your client is a Hollywood animation studio). I attached Excel file because this doesn't paste well. Assume PK is a category, Name is the famous mouse and Desc is extra info. Haven't tested this but you should get the idea. ` 'Lets say earlier you set strSearch = ""Rizzo"" because thats the non-key field you're searcing on. 'You can search by the R in Rizzo via SetBrowseFilter to make it faster Set oUDT = oSession.AsObject(oSession.GetObject(""CM_UDT_svc"", ""SY_UDT_FAMOUS_MICE"")) retVal = oUDT.MoveFirst() Do Until cBool(oUDT.Eof) = True strFilter = Left(strSearch,1) : strName = """" : Found = 0 retVal = oUDT.SetBrowseFilter(strFilter) 'This filters the pending MoveNext and makes it MUCH faster retVal = oUDT.GetValue(""UDF_NAME$"", strName) If UCase(strName) = UCase(strSearch) Then PK = oUDT.GetKey() 'gets the PK value retVal = oUDT.SetBrowseFilter("""") 'Important to reset this for next attempt Found = 1 Exit Do Else retMove = oUDT.MoveNext() End If Loop If Found = 0 Then 'show messageBox no results found `

Thank you all so much! I'll give it a try & see where I end up, but I think that example @AlnoorCassim is exactly what I need! Love the 90Minds group!!

More importantly Boise which famous mouse did you identify with?

I am out of my league in so many issues here. Alnoor lost me at ""let's say earlier"". But since I do know one thing about Access and that is PTQ's I will toss this out into the realm of possibilities but I don't even know if it is possible. Could you create a PTQ table of the user controlled UDT, create a select query table of some sort that is multikeyed which your script can access and pull the required data? @DanBurleson does incredible things with scripting and access tables. Wondering out loud if this might be a tool in the old kit. Again, don't crucify me if I am truly crazy.

There is something to be said about keeping it all under the MAS umbrella even though the Access database engine is built into Windows and users would not need Access installed. I am curious whether an Access query would out perform a sequential search. Access will load the entire table into memory which, depending on the size of the UDT, could be faster. Sounds like a snowy day project for the truly crazy.

HAHA @AlnoorCassim your list was missing the most important famous mouse & my personal fave... MINNY MOUSE!! DUH! :)

Corporate mice don't go on my table. Besides you're mistaken it's Pinky.

i know this might be considered an old post but i was wondering @AlnoorCassim , would a GetResultSets not be more efficient? i have never tested it on a UDT but i was just curious.

Hello David - Welcome to the group. Now please change your profile pic! :) Otherwise @JeffSchwenk will institute the 90 Minds new member hazing on you. GetResultSets is very efficient yes but overkill for UDT searches. Instead use UDTWhereClause. This is what above could be replaced with potentially (untested of course): strSearch = ""Rizzo"" Set oUDT = oSession.AsObject(oSession.GetObject(""CM_UDT_svc"", ""SY_UDT_FAMOUS_MICE"")) strFilter = ""STP(UDF_Name$)="" & Chr(34) & strSearch & Chr(34) oUDT.UDTWhereClause = strFilter retVal = oUDT.SetMode(""SET"","""","""") retMove = oUDT.MoveNext() If retMove = 1 Then PK = oUDT.GetKey()

That reminds me I need to haggle @TroyTurrentine for the recent picture taken at our last user group... Regarding the UDT, I've hardly utilized them in scripting so far, so I wasn't aware of that UDT where clause, good to know though. Thanks for the tip.

@DavidSpeckII - Come to MOTM 2018 and sit for a head shot.