complex lookup

[bob muzik]

I have a table named Tapedata which contains two fields, Record and Artist. They both have lookups to a table called Forty. Using the "edit field rules", I have it set up so that the fields record1 and artist in Forty fill in Record and Artist. I type a few characters in Record, hit tab, and both the Record and Artist field are filled. If there is more than one record with the typed characters in the Record1 field, obviously I then have to make the correct selection from Forty. This works just fine. However, in some cases, the input I make in Record is not contained in Record1 but it is in Record2 of Forty. In this case, I can't fill Record because, to my knowledge, you can't specify more than one field to lookup in a single lookup table and you can't map more than one field from one table into another using "edit field rules". If you can, please tell me how. As an alternative, I made a duplicate of Forty with another name. I then tried to define a conditional lookup in the field rules using Forty and its duplicate. The problem I have is how to specify the condition for the lookups. I thought about using *any or Contains but I don't know how to specify the different tables and their fields and what to specify generically for the character string to look for. Can anyone tell me how to write an expression for the conditions so that when I type something into Record, the lookup will occur and the conditions will switch the lookup, if necessary, from one table to another?

[Tom Cone Jr]

RE: complex lookup

I suppose it might be possible to write an xbasic script that would accept input, and then search both fields in your lookup table, returning whichever value was found first. I don't think you can do it using only field rules.

Have you considered simplifying the structure of the lookup table to just have a single 'record' field? It shouldn't be too hard to export all the record2 values, and then import them back into record1 fields. Once this has been done and checked you could change the structure of the table, to eliminate record2 field altogether.

Apart from doubling the number of records, would this cause other problems for you?

-- tom

[smtobias]

RE: complex lookup

You might want to try a conditional lookup, Bob. I use these for looking up data in more than one table. You could put a logical field on your form or include parameters in the lookup to select the table you want to search. Then only the records in that table come up. You are able to set up parameters for each of the tables you assign to the lookup.

Sylvia

[Stephen Williams]

RE: complex lookup

The key to Sylvias method is the condition. How do you know which table to look up?

Presumably you start by looking in record1, and if it is not there, THEN you would look in record2.

There are a number of ways to do this depending..
1. create an expression based on key_exists()if the fields are indexed.
2. use tablecount() with a filter expression.
3. I am sure there are others...

I rather like tablecount() and tablesum() functions as they let me do some very direct data integration. they only accept character value parameters, one of which is a filter, and getting that filter part figured correctly is worth a beer when it's done.

Let us now what works for you and what doesn't.

Steve

[bob muzik]

RE: complex lookup

I looked up the Key_... that you mentioned but it seems that it is only used within the table you are working in, not the lookup table. I like the *any function but I don't know how to write the expression because I don't know the proper syntax. For instance, *any requires (characterexpression1,characterexpression2) butI need to specify the table and its field prior to characterexpression. How do you write- forty,record1 with the proper syntax? You can't type the table name followed by an arrow pointing to the field like is shown in the manual. Understand that the conditional lookup allows me to specify more than one lookup table and then map the appropriate field(s) of each lookup table to the master table. However, one must specify the condition and as mentioned above, I don't know how to write the expression for the condition.

[Stephen Williams]

RE: complex lookup

Bob-
If you like give me the names and types of the fields and tables involved and I will give it a go. I have done several recently and feel ready for the challenge. Actually, its pretty straight forward.
1. pick your function and the result you want to go in the filter position. You may want a condition as
tablecount("Forty","Record1 = 'Barry Manilow')=0
(i would...)
2. open up an expression builder box
3. Play with the filter portion only until you see the expression the way you need.
4. Copy it to the clipboard and paste it into the function in the expression builder and evaluate it till you get a result not an error...

A stab might be
sought = c variable of who you want to look for
tablecount("Forty","Record1 = '"+sought+"'")=0
then look in Record2.

Heck...with these functions you could set up a little form with a variable seek ="whatever-whowever"
and it would TELL you which field(s) had a match(s)...

Steve

[bob muzik]

RE: complex lookup

I'm willing to let you try but there's something seriously wrong here. When I go to "edit field rules" and select a "conditional lookup", I should be able to state the conditions in a generic way so that, regardless of what I type in the field for which I've defined a lookup, the condition will apply. I shouldn't have to get any fancier than that. What's happening is when I create an expression, I generally get syntax errors such as too many characters, etc. In addition, even though I specify the lookup tables, when I designate them in the expression, Alpha says it can't find them.

As you requested, all fields are character fields. The table I want to update is Tapedata and the fields are "record" and "artist". The lookup table is "forty". It's fields are "record1", "record2" and "artist". My normal lookup is a table lookup and I use the "record1" field because that is where most of the data for "record" will be found. For a conditional lookup, I duplicated "forty" and called it "forty1".

[bob muzik]

RE: complex lookup

Stephen Williams, I gave you the information you wanted. Are you still helping or have you been too busy? Any help is appreciated. One thing I did notice on a lookup is that even if I use a *any function in my conditions, Alpha only looks at the first word in a field.

[Tom Cone Jr]

RE: complex lookup

Bob, I've looked at your problem. I don't think it can be effectively using the conditional lookup field rules based on values being keyed in to a new record in Tapedata. Maybe more experienced folks than me can suggest a specific way to do it, so I'll be watching.


If you don't get other ideas, consider the following alternatives:


1) Run an operation against your Forty table to create new records, moving all record2 values into new records, but storing them in record1 field. Then change your structure to strip out the record2 field altogether. In the future if you want to describe the same album in different ways just enter multiple records. Your lookup would be on a single field, record1, and life would be much simpler.


2) Define two forms which contain browse objects based on the Forty table. These will be alternative lookups. One will be sorted on record1, and won't show record2. the other will be sorted on record2, and won't show record1. Include a button on each form, to display the other form if the desired value is not found in the current form. the user begins data entry, as the Record field is activated the first form loads automatically. The onInit event script for this form tries to 'find' the partial key already typed by the user. If a match can't be found, the script closes the first form, and loads the second.


-- tom

[Stephen Williams]

RE: complex lookup

Bob -
sorry - I got busy with the season...
I started out trying to create table structures from your first post, to be sure I understood your arrangement.

table: Tapedata
field1: Artist
field2: Record

table:forty
field1:Artist
field2:Record1
field2:Record2

table forty was duplicated in an attempt to allow lookups into field2.

As far as I can tell this is as far as you got, am I on track?

My first reaction was how come we have two fields for Record? I think you may be needing a better organisation of data amongst the tables. You should only have one entry for each Record, which includes the Artist (usually only one per record), rather than one entry for an Artist with multiple records. Where you have one record with multiple Artists I might create an entry for each artist so I could find it.

I see Tom Cone is suggesting pretty much the same thing...

Steve

[bob muzik]

RE: complex lookup

This makes me feel like the conditional table lookup is useless unless you can specify something like a total as the condition. I like Toms first idea but the second is too complicated. For stephen- the table called Tapedata is a table that i use to track 45 rpm records that i have taped on 10 inch reels. when i type in Record, the lookup is to Forty. There is a Record1 and Record2 field in Forty because a 45 rpm record has different songs on each side. without getting too detailed, the artist is normally the same for each song. Normally, only one side is any good and i put the title for the best side in Record1 and the other title in Record2. On a few occasions, i also tape both sides. That's why I want to be able to automatically access Record2 if Alpha doesn't find what i type in Record1. Since i don't need to go to Record2 very often, I usually just turn off the field rules and type in the information and then go back and turn the field rules on. I was hoping that the conditional lookup would save me some time. Thanks for all the help. If someone happens to figure out how to do the conditional lookup, please let me know. Maybe i'll post another message in the future.

[Stephen Williams]

RE: complex lookup

Bob -

The simplest solution would be to add a field "side" ( 1 or 2) and have only one field for record.
make a copy of your tables first then add the field side and fill it with "1" for all records with a record1 entry. Next make a duplicate and append from your duplicate all records with a non blank record2 at the same time updating the side field to "2"
I am sure you can figure this out...Then you only have one field to search, plus if you have different artists on each side that gets tracked also...It will be very easy to sift,sort and search.

Steve

[Tom Cone Jr]

RE: complex lookup

Bob, the problem you're having is because the conditional table lookup field rule (that's a mouthful!) is based on conditions present in the table to which you are entering data. i.e. Depending on a 'condition' which exists in the record you are working on, go lookup values from either table1 or table2.

Your problem is different. You want the rule to decide whether to use table1 or table2 depending on 'conditions' in *table1*, regardless of what values are present in the table you are working on.

I don't think the field rule was intended to be used like you want. In a sense you are trying to turn it on its head.

-- tom

[JohnZaleski]

RE: complex lookup

If I understand the situation then I absolutely agree with Tom and Stephen that a redisign of the file structure with only one "song or record" per record is best. Not only lookups, but reports, browses and all of Alpha's functionality will be easier to use. That being said, here's a few general ideas:

1) Create a global variable "RecordSideToSeek" and use the value of that variable as the condition in the conditional lookup field rule. Put a button on your form that toggles the variable's value as either "1" or "2". This isn't elegant, but you could do a lookup on "side 1" and if you did not find it exit the lookup, hit the button and then when you do the lookup it would be to "side 2". When you toggle the button you could also change the color to show which is the current value. Extra keystrokes here, but if the "side 2 or record2" is a small part of your database, maybe not so bad.

2) Xbasic Code Attatched to a Button: Ask for the artist or title. Depending on what the user answers do a query, populate an array with the query values, the artist and the title, and present the choices as a list box UI_LIST_ARRAY. If something is selected you could set the values of the selected choice into the two fields.

[bob muzik]

RE: complex lookup

Thanks. I moved the record2 values into record1 as suggested by Tom. Some of the ideas you gave me are very good. I printed them out and will work with them as I get time.