Im looking to add a XUserSlect to a layout  "dispatch" looking up the customers name from the "customer" layout. my question is my customer select field is HUGE, is there a way to type the first few letters of the customers name in the primary field to reduce the size of the user select field?


I kinda feel like the boy who found a genie in a bottle, Im afraid to waste any of my three wishes on stupid questions.

Yes, it can be done easily using xuserselectr function. There was quite a discussion about it on this forum. I will look up the thread and post it here. I use this all quite extensively.

I think this is WAY over my head at the present time. thank you for the direction.

Probably you already know that x-family of commands and functions cannot be tested in the Preview mode. You are better of saving your application appended by test or reconciling it first. I prefer the first method. If it is Customer.dsr, I save the application as Customertest.db.  When everything works as you want, you may reconcile it to customer.db

See if this helps.

oh my it works!!! I been working on it to long, I was forgeting to put the dot dot on the end of it! your good! thank you so very much for the help!

I found that out about if you know the code it doesnt waste time on the pick list. Only thing is when I type " ..abc.." it pulls up the whole list "abc.." works nice and "..abc" seems to work. but im not complaining, im too happy about getting past this first hurdle. thanks for the time and all the help!

no, i did not. and when I did (put it in global) it made an error on evevry xlookup and all other functions in program. strange

I have implemented abc..  and ..abc..
I do not see any value of ..abc if both above are working.

Global Code
______________

var vFieldName as String
var vStart as String
var vEnd as String


// Finds all values in vVals that contain vMatch. Returns matching
// values as a semicolon separated list.
Function FilterValueList(var vVals as String, vMatch as String) as String
var vList as String
var vItem as String

vList = ""
While @Len(vVals) > 0
{
  vItem = Split(vVals, ";")
  If @Instr(vItem, vMatch) > 0
  {
   If @Len(vList) > 0 
   {
    vList = vList + ";"
   }
   vList = vList + vItem
  }
}
return(vList)
End Function


var vApplication as String
var vTarget as String
var vDisplay as string

//For PickList

Subroutine PickList_choose( )
var vVals as String
var vFinalList as String
var vSearch as String

If @Right (vfieldName, 2) = ".." and @Left (vFieldName, 2) <> ".." then
     {
     
           vStart = @left (vFieldName, @in (vFieldName, "..") - 1) + @chr (1)
           vEnd = @left (vFieldName,   @in (vFieldName, "..") - 1) + "zzzzzzz"

     }

If @left (@(vFieldName), 2) = ".." and @Right (@(vFieldName), 2) = ".." then
     {
           vSearch = @replace (@(vFieldName), "..", "")
           vFinalList = ""
           vVals = @XListValues(vApplication, vDisplay) 
           vFinalList = FilterValueList(vVals, vSearch)
           @(vFieldName) = @UserSelect(vFinalList)
        
         If @in (@(vFieldName), "/") > 0 then
           @(vFieldName) = @Replace (@(vFieldName), "/", "//")
           //writeln (@(vFieldName))                  
           
           @(vFieldName) = @xlookup (vApplication, @(vFieldName), "Display1", vTarget)

     }

End Subroutine

======================

Dx1  on element exit event    (Dx1 is the name of element)
____________

If @left (Dx1, 2) = ".." and @right (Dx1, 2) = ".." then

     {
           vApplication = "data\diag.db"
           vTarget = "DiagCode"
           vDisplay = "Diag!Display1"
           vFieldName = "Dx1"
           picklist_choose ( )
           
     }

if @Right (Dx1, 2) = ".." and @Left (Dx1, 2) <> ".." then

     {
           vFieldName = Dx1
           picklist_choose ( )
           
           Dx1 = @xUserselectR ("data\Diag.db", "Diag!Display", "DiagCode", vStart, vEnd)
     }

that works very nice, ..abc.. pulls up the pick list but when I select the item it does not put it in the vFieldName. abc.. works normally. need to recheck my spelling

Dx1  on element exit event    (Dx1 is the name of element)
____________

If @left (Dx1, 2) = ".." and @right (Dx1, 2) = ".." then

{
  vApplication = "data\diag.db"
  vTarget = "DiagCode"
  vDisplay = "Diag!Display1"
  -->vFieldName = "Dx1"
  picklist_choose ( )
   
}

if @Right (Dx1, 2) = ".." and @Left (Dx1, 2) <> ".." then 

{
  -->vFieldName = Dx1
  picklist_choose ( )
   
  Dx1 = @xUserselectR ("data\Diag.db", "Diag!Display", "DiagCode", vStart, vEnd)
}

=======================

Please note in the above part of the code which is marked by red arrow:

The code at the first red marker takes the name of the element as the value and use it as a indirect reference,  while the code marked by second red arrow refers to the value of the element. I thought not putting or putting quotes might make that difference.

case I messed up here is my global code

var vFieldName as String
var vStart as String
var vEnd as String


// Finds all values in vVals that contain vMatch. Returns matching 
// values as a semicolon separated list. 
Function FilterValueList(var vVals as String, vMatch as String) as String 
var vList as String 
var vItem as String 

vList = "" 
While @Len(vVals) > 0 
{ 
  vItem = Split(vVals, ";") 
  If @Instr(vItem, vMatch) > 0 
  { 
   If @Len(vList) > 0 
   { 
    vList = vList + ";" 
   } 
   vList = vList + vItem 
  } 
} 
return(vList) 
End Function


var vApplication as String
var vTarget as String
var vDisplay as string

//For PickList

Subroutine PickList_choose( )
var vVals as String 
var vFinalList as String 
var vSearch as String 

If @Right (vfieldName, 2) = ".." and @Left (vFieldName, 2) <> ".." then
{

  vStart = @left (vFieldName, @in (vFieldName, "..") - 1) + @chr (1)
  vEnd = @left (vFieldName,   @in (vFieldName, "..") - 1) + "zzzzzzz"

}

If @left (@(vFieldName), 2) = ".." and @Right (@(vFieldName), 2) = ".." then
{
  vSearch = @replace (@(vFieldName), "..", "") 
   vFinalList = "" 
   vVals = @XListValues(vApplication, vDisplay) 
   vFinalList = FilterValueList(vVals, vSearch) 
   @(vFieldName) = @UserSelect(vFinalList)
    
     If @in (@(vFieldName), "/") > 0 then
  @(vFieldName) = @Replace (@(vFieldName), "/", "//")
  //writeln (@(vFieldName))   
   
  @(vFieldName) = @xlookup (vApplication, @(vFieldName), "Display1", vTarget)

}

End Subroutine

Looking at your code, it seems like you are displaying a list of Customers after putting ..abc.. value in Company Name element and after selecting selecting a customer name, you want to place that in Customer Name element. Is that correct?

I also have this same thing on another form that looks up vendors. I plan on using this on parts lookups and inventory, I see endless opportunity&#8217;s. All is good
looks like I owe you an airplane ride! thats about the only thing im good at

hahaha, no nothing as interesting or glorious as a jet jockey more like a taxi with wings. Still the worst day flying is better than the best day working.
p.s. the code works very good

Quote:


As I understand you want to have list from Subform. Which form has the key value, Parent or subform?

You are trying to get the list from subform and ParentForm has the key. You will have to get info in the subform. For that you will have to make one more element that takes the key value from the parentform, hide that element, so it does not take the realestate of the subform.

Intially you can mass update but later on for new as well as updated form, it should automatically take the key value into the subform.

Once you have the key value in subform, the rest should be easy.

had to look that one up, Its natural linking.

I have done some work in sample database, Countries, that is a typical database with subform named "Cities". I created on element in subform named "Country"  and updated its value from ParentForm "Countries".  I used following Code for Mass Updating

var vName as String
var i as Int


For i = 1 to @FormResultSetTotal("Cities")
	FormFieldValue ("Cities", "Country", i, @FormFieldValue("Countries", "Country", @FormResultSetCurrentPosition("Countries")))

Next


 

It produced the desired result. Now subform have "Country" element which proper country.

I made one other form named Display, which has just two elements namely - Country and City.

I can get the x-family work on the parent form, say getting the name of the Country from the parent form, but using the x-family commands on subform elements and layouts failed miserably. The following command in Display layout did not yield anything...

vString2 = @xlookupall (@FN, Country, "Cities!Country", "City")
writeln (vstring2)


PopulateListElement( City, vString2)
 

or

var vString2 as String



vString2 = @XLookupSourceList(@FN, Country, "Cities!Country", "City")

PopulateListElement( City, vString2)

 

Attempt is to narrow down the list of the cities having already key is being established in subform. I have not been successful so far.  That leads me to ask, do these commands (x-family) works to get the data from subform, or it needs to have the form at the same level?

I have this programed in (ON ELEMENT EXIT)  on what I have as the  DISPATCH form or your display form.

var vString2 as String



vString2 = @XLookupSourceList(@FN, Company name, "equipment list!co_name", "Equipment type")

PopulateListElement( machine type, vString2)

with no errors but aslo with no results. I did a manual update on the equipment list as its so small at the moment

Thanks Bharat , e-mail sent

The userslect works well, but I would like it to auto type other fields from the same to LEs on the main form that would be like xlookup filename(newdispatch), key(company name), form name(equipment list)!ext key(company name), a second key field (equipment type) then target. 
and then i want it to guess the winning lotery numbers. well maybe not that the lotery ticket thing. am i asking too much?
its so I can pick out a piece of equipment on the dispatch form and it fills in the model and serial number. the more I study the more it says no or least not with a lookup that im used to.  I have the natural links or could it be done in relative links instead?
Thanks always

No, I think your on target. its me having difficulty understanding. When you put it like you just did it makes a little more sense. Im going to try this. You are being more than helpful.

Bharat
After studying the code you sent, I now have more questions than answers.  I will use the name of the db you sent me to keep it simple.

Im using a converted Q&A database and in the DISPLAY form it has a text field for the COUNTRY form element not a combo box. I will type in the country in manually or more accurately it will be filled in already by the time I get to the CITY form element.

I would like a userselect for the CITY form element, thats also a text field, that I can choose to fill in the CITY form element and automatically fill in the POPULATION form element on the DISPLAY form.

Do I need to change over to combo boxes? Thanks for your help

Quote:


To emphasize the above point, let us go to the begining of the link, where you inquired if it was possible to narrow down a long list in the useselect list. We, indeed, went really long way using filtering function, loop, subroutine and achieve our goal halfway. ( I said halfway because we did not tackle ..abc search)  

The same thing (and more as it also include ..abc search) can be accomplished in just one statement.

Let's say, you want to have CustomerName from Customer.db - Customer_Info Form where it is in Name element

On Element Exit Event

If @in (CustomerName, "..") > 0 then
     
CustomerName = @Popupmenu (@xlookupall("Data\customer.db", CustomerName, "Customer_Info!Name", "Name"), "Customer Names")

That is it.  With Sesame more deeper one goes, more pearls he finds!!!

Nothing wrong with the previous approach but that was before version 1.1.  

For this you can use also userselect or Combobox. The important thing is that you are getting the string of values, and here, we did not have to manupulate string. Thanks Mark, for giving @xlookupall function which is very useful for working with One to Many (or Many to Many since we can use search) relationship.

[quote author=Bharat_Naik  link=1111180599/45#46 date=1112283132]
On Element Exit Event

If @in (CustomerName, "..") > 0 then
     
CustomerName = @Popupmenu (@xlookupall("Data\customer.db", CustomerName, "Customer_Info!Name", "Name"), "Customer Names")
[/quote]

thats exactly what I had in mind for the primary field. in my example
Machine type = @Popupmenu (@xlookupall("Data\newdispatch.db", Company Name, "equipment list!co_name", "Equipment type"), "equipment type")

I cannot do an xlookup on the equipment type from the equipment list form because many customers have the same equipment. I would like to pull the model and serial from the selected subform to add to my main form. Is that possible?

First off I would like to thank you Bharat for your time with me on this. Also to the many others that help. I have been studying this for about four months and any frustrations I have are from not being able to understand or comprehend what is being told to me. I do so much appreciate everyone&#8217;s help and patients with me.

Sometimes it is easier to create an XLU into itself, the same file, looking in the same field, to get a list of existing values to choose from.  Then, every time a new value is added on a record, that LU list will be increased.   That eliminates the need to make a separate database for values to use in that element. 

You could also program the element not to accept anything that is already added once you have an example of every desired value established.

Once again, WELL DONE Bharat_Naik!

Thanks for the code example. 

I am usually quick with the ideas, but don't have the time to put together the code for theoretical examples.   But you understood exactly what I meant. 

A variation could populate a combo box vs. a pop-up menu.

Hello Flip,
This time you are very thorough in your description of query and problem you are confronted with. You do not have the recognizable unique field the Equipment_Type subform. I said recognizable as eventhough, serial number is unique but you would not know what does it represent just by looking at it. You do have two elements in the subform, namely Customer and equipment, if somehow if you join it, that will become unique. The important thing is that you already got both of them in Dispatch form. You never have to even look at it. Let's say you combine first 6 letters of customer or company's name and first six letter of equipemt type combine and make unique value in the subform and assigned it to newly formed element which is always hidden, you never have to look at it.  Ofcourse, one has to remove any white space by programming. Now you have 12 letters unique key. You may make it less, if you please.  You do not have to make any additional element in dispatch form.  That is it. Now you can autotype all the required field, including serial# etc.

Seems like a little inefficient, but workable. Let's say you make one element in Subform name UniqueKey

var vCompany as String
var vEquipment as String

//Removing white spaces

vCompany = @replace (Company, " ")
vEquipment = @replace (Equipment_type, " ")

//making UniqueKey by combining first six letters of each

UniqueKey = @left (vCompany, 6) + @left (vEquipment, 6)

Hide the UniqueKey Element after you make certain it works well, this will save some realestate in the subform.

In the Dispatch form in Element Exit Event of EquipmentType

//You have already gotten company and EquipementType

var vUniqukey as String
var vCompany as String
var vEquipement as String

vCompany = @replace (Company, " ")
vEquipment = @replace (Machine_type, " ")

vUniqueKey = @left (vCompany, 6) + @left (vEquipment, 6)


//Now the xlookup

SerialNo = @xlookup (@FN, vUniqueKey, "Equipment List!UniqueKey", "SerialNumber")

Model = @xlookup (@FN, vUniqueKey, "Equipment List!UniqueKey", "Model NO")

I might be way off on the naming of elements and forms but you know it well, so I am not verifying with your note.

You have some white spaces in the name of Elements and form names. It is not a good Idea to have that.

Please let us know what happens.  I could not possibly test this but conceptually this should make things clear.

You dont know it but you have explained what I need to do. The Equipment type needs to be modified to have not only the name but the model and serial numbers as well. All in the one field. I do not need to separate all the different fields. See!! You did make it look so simple. This will work out very well and save a lot of time in data entry and in programming. 

or like you said earlier.....

I can add a key number after the name like
(equipment name  0123) making the equipment name a key field for me to do the xlookup.
is there a way to hide the number field from view. not an issue if not.   

is there a way to add this number key to the end of the equipment name? in a mass update!
oh great!  now Im talking to myself.
my hair is on fire, im so glad its friday  8)

another nice idea, thank you for you help on this. Im so glad this works. I was about to call it quits but am very thankful you stayed with me and helped me. thank you and Bob and Carl and all great folks for your support! oh! and Lantica and the folks over at Hammer Data!  and ....*the music comes on and someone shouts  GET OFF THE STAGE!!* 

I will give this a try in the future. most important is to thank you for helping me