Lookup Question

[betty]

I probably already know the answer here - but anyway -

I have a jobcost table and an item table. I want to use lookups to get the value from the item table and populate fields in the jobcost table.

If I use a field lookup - it will change the information in the file won't it - if that field used to do the lookup ever gets changed or if 'recalucate calculated values' is used won't it?

I read somewhere about two different ways to do a lookup - and one of them might change the data in the future - but the other would not. I can't find that back again ...

Can somebody tell me what I am thinking of?

Thank you.
Betty

[Mike Wilson]

Re: Lookup Question

Betty,
Using a field rule table look up will not change the value of the field being looked up. So, I am not sure what your thinking of.

[robert]

Re: Lookup Question

I think what he's saying is that, if he uses a table lookup on the item table to fill in the job table, will the values in the job table change if the values in the item table are changed later. I don't think it will because a new lookup won't occur until someone attempts to change the value in the field calling for the lookup in the job table.

correct me if I'm misinterpreting.

[CALocklin]

Re: Lookup Question

Maybe this will help with the understanding....

First, let's be clear for everyone that we are not talking about a "find record" situation here. I don't think the original poster was talking about that but I want to be sure it's clear for anyone reading this. I've been involved in other situations where this wasn't clear and caused a whole lot of confusion.

There are two basic "lookups" that involve looking up a value (or values) in a table.

The first one is a "field rule table lookup". This is a "built-in" lookup method that requires the user to click a drop-down icon on the field to select a value. This type of lookup also allows you to easily "import" (I use that term loosely here) other fields at the same time. For example, You might be selecting a client name and want to also bring the client number into the new record so the user could easily run a look up or sort by whichever one they have or are more comfortable with. This "field rule table lookup" is basically a "one time" thing - changing the data in one table will not affect the other in any way UNLESS the user actually clicks the drop-down and selects a different value.

Sometimes a "lookupx" function (where "x" is actually C, N, or L) might be used in a calculated field. Example:
lookupc( "F", Broker_Nof, "Brkr_namef", "Clients", "Broker_no_")
This, I would hope, is extremely rare. In most cases it would be a sign of poor database design but I'm sure there are some cases where it makes sense. In this case, since it's a calc field, running the "recalculate field rules" action (or whatever it's called) would change the value if the value in the Clients table had changed. This is why it's not generally a good thing to use one of the lookup functions in a calc field.

Even in the example above, it wouldn't be uncommon for a client to be assigned to a different broker. Or, if that particular client with the original broker number was deleted, the new value would be blank. Both situations would make a mess of the data which is why this usually isn't a good idea.

Another place that I've mentioned I don't like to use a calc field (but I'm sure many still do it) is for "last update/change" dates. Many make this a calc field equal to "date()" but that will change whenever a global update is done or the "recalculate field rules" is run. The best solution I've found for this is to put a short script in the CanSave event:

tc = table.current()
tc.change_dtf = date()

Of course, you would replace "change_dtf" with your own field name.

HINT: Always use the same field name in every table. That way you can just cut and paste the script. Or, you can save it as a keyboard macro as I do. I just type "jjchgdt" and the macro types my full script shown here:

tc = table.current()
tc.change_dtf = date()
IF eval_valid( "tc.change_tmf" )
    tc.change_tmf = left(time(),5)
END IF
'used only if a *script* creates the record but doesn't include the creation date:
IF tc.create_dtf = {}
     tc.create_dtf = date()
END IF

(I really do find it hard to believe that so many programmers do not use a keyboard macro program. I'd go crazy without it.)

[Mike Wilson]

Re: Lookup Question

Cal,
Nice explanation.

(I really do find it hard to believe that so many programmers do not use a keyboard macro program. I'd go crazy without it.)

Is this something you have written with A5 or is this a third party addin? I'm nebulously envisioning that this could be done with an A5 UDF.

[CALocklin]

Re: Lookup Question

Thanks.

While it might be possible to create some kind of keyboard macro program in A5, it definitely wouldn't be worth the time it would take. (And I wouldn't know where to start.)

The one I use is called Macro Express and costs $40 today. I will happily send a copy of my xbasic macros to anyone who buys it. And, in case you're wondering, my only relationship with them is that I bought a copy of their program years ago.

[robert]

Re: Lookup Question

So where's Betty to tell us if we got it right or not? I have to apologize because in my response to her I used "he's" instead of "she's".

[jkletrovets]

Re: Lookup Question

While I don't use a keyboard macro program like Cal. I do use Clipmate. It is a windows clipboard replacement that lets you create a library of commonly used clipboard items (in my case code I use all the time). It is a very nice program. The other thing it does is keep history of what you put into the clipboard using ctrl+c. Before Clipmate...I was constantly wishing I could have the item I put into the clipboard 3 or 4 clips ago. With Clipmate...that is no problem. Keeps a history of what you have put into the clipboard and you can go get them and reuse them. Very cool stuff IMHO.

Regards,

Jeff

PS - Hey Cal......would you be interested in selling your message box code you posted recently in another thread?

[betty]

Re: Lookup Question

Yes. Cal's explanation is the thing that I thought I had read.

Thank you.
Betty

[Peter.Greulich]

Re: Lookup Question

While I don't use a keyboard macro program like Cal. I do use Clipmate. It is a windows clipboard replacement that lets you create a library of commonly used clipboard items (in my case code I use all the time).

Doesn't anyone use the code library? Isn't that what it's for? It's always right there when your coding. I know that Cal's keyboard macros go way back before there was such a thing as a code library, but otherwise...???

[jkletrovets]

Re: Lookup Question

Doesn't anyone use the code library?

Actually Peter....I never even knew that was there!:o

The one benefit to Clipmate is that you can use it in any windows program....not just Alpha.

[Peter.Greulich]

Re: Lookup Question

Actually Peter....I never even knew that was there!:o

The one benefit to Clipmate is that you can use it in any windows program....not just Alpha.

That' ok. Every now and then I stumble across something that I didn't know was there myself. I think I used to have a copy of clipmate, or maybe it was something similar. But I highly recommend the code library!

[jkletrovets]

Re: Lookup Question

I was just reading the help file on it. It does seem to have some advantages. Kinda cool you can run Xbasic code directly by double clicking on an item in your code library!

Thanks for the head's up........