Okay all,

I'm brand new to Sesame - am having to work on it for a client; but I've been a PHP/mySQL and MS-SQL junkie for the last 2 years...  So SBASIC is all new to me, and I'm having to mentally re-adjust to the limited access and methods that Sesame uses to access the DB.

I'm trying to implement something that SHOULD be possible, but I'm not sure what the best method is to implement it...  I have a Database of family members, and I want to create what I would term in SQL as a "foreign key" relationship between records.  Tips, suggestions, or advice are certainly appreciated!

Basically, I have a numeric field "personID" to uniquely identify each person/record (a "primary key" in SQL terms).  I also have the field "fatherID" - the idea being that this field will refer to an existing "personID" record if that person's father is already in the database, so that parent/child relationships can be tracked.

However, I don't want to require the users to remember the actual ID numbers - so I was hoping to display a list-box or combo-box in the form, and have it populate (On Form Entry) with the names of all of the males currently in the database.  When a selection is made, the ID number of that person could be stored in the "fatherID" field; and on future viewings, the name of the selected "father" could be automatically retrieved and displayed on the form.

Of course, the first issue is that the combo-box needs to display text information (the potential father names), while the value to be saved is numeric.  Maybe I'm spoiled, but in HTML forms you can have combo/list-box items display one thing, but their actual value be something else entirely (i.e. a "key-value" pair).  I can't see a way at the moment (except perhaps with a static array or something??) to retrieve the values from 2 columns of a database at once, and keep them associated with each other while the user selects one.  They need to stay associated to guarantee that the proper ID number is recorded - (i.e. going back and looking up the father's name to retrieve his personID from the database after selecting him is not acceptable, as the names themselves are not going to be guaranteed to be unique).

Any thoughts, comments, concerns?  I see a couple of possible ways of doing it; but I'm hoping your suggestions will point to a clearer or less-cumbersome solution than what I'm envisioning...

Thanks, take care,

--Noel
noel_wade@hotmail.com

If your name/number pairs are coming out of database records (as opposed to being static lists) you can use commands like XLookupSourceListAll to create a composite value for your combo box or picklist and parse the number off the selected value.

It is one of the new commands added in a recent update. Everything added since the original release is contained in a Documentation Supplement that is available in the same place you go to download the program updates. Take a look at the new X commands, and also the StringArray functions, whcih are very handy for operating on the values returned by the X commands. 

For a brief summary of the new commands, take a look at the Change Log.

You may want to consider looking into the Inside Sesame newsletter. I know that recent issues have included some articles on different ways to generate and work with picklists and combo boxes.

Quote:


@XLookupAll will return a semicolon separated list of values. @SortStringArray can sort the list. PopulateListElement can hand the list to a combo box. You'll have to use the string array functions to create a custom duplicate stripping routine.

Erika,

I've got the programming done and I will post it in the programming examples section of this forum, but I have 1 more issue.

Combo box's dont seem to follow the traditional  'programming events' rules. On Element Entry and On Element Exit dont do anything. I THINK I could only get the program to fire when I used On Element Immediate change.

My programming works fine if I put it in a command button.

Is this documented somewhere in the manual, or is this a bug?

The below program was pasted in LE named MFGR On Element Exit:

var vduped as string
var vstring as string
var vcollections as string
var vcollection as string


if @mode() = 2 and mfgr <> "" then
{
vcollections = @XLookUpAll(@FN, mfgr, "mfgr", "collection")
vduped = @sortstringarray(vcollections, 0)

while @len(vduped)>0
{
if @instr(vduped, ";")>0 then vstring=split(vduped, ";") else vstring = split(vduped, @newline())
if @instr(vcollection, vstring)<1 then vcollection = vcollection + vstring + ";"
vstring = ""
}
populatelistelement(collection, vcollection)
}

Edit: MFGR and Collection are both Combo Boxes.
Edit again: Program does not work in MFGR On Immediate Change, either.

Thanks for the help!

Steve

I had at least 5 writelns in the program until I got it to work with a button. With a button, it works flawlessly.

I then pasted the same program into the combo box, in serveral different events, but no luck.

In particular (and maybe the only issue) is the second combo box is not supposed to fire unless the first combo box is not blank.

Is it possible the second combo box cannot tell if the first combo box is filled in? (a bug?)


Steve

Oh, good point. I just added writelns to the combo box on element exit and on element entry and tested it in preview mode.

the results........NOTHING!

Just for kicks I pressed the button to do the xlookups and populatelistelement. The results.....CRASH!

I'm restarting the server now becuase Sunlock wont unlock the dsr.

I guess thats what I get for trying to do an xlookup in preview mode.

Steve

edit: restarted sesame server and tried to open DSR file.

ERROR: Database does not exist.

Go figure!

Erika,

I see whats happening now.

in Update mode, my combo boxes seem to be working fine.

I was testing my program in Search(retrieve spec) mode.

I put my programming in On Element Change and it is working fine for me, but as mentioned before, it wont work in the other modes AS LONG AS IM IN SEARCH MODE.

Is this the correct functionality?

Sorry for the trouble.
Steve