field rule that uses XLOOKUPC

[luthier]

I have a field rule that uses XLOOKUPC to choose a discount rate for inventory items based on the retail price, using a database called DISCOUNT.DBF. It works fine on when I use what I call my server computer (XP), where the data folder is. But when I use Alpha Four on a local computer (also XP) on the network, when I go to the inventory database, it tells me "Unable to find/create database: Discount"
All of my databases, including Discount.dbf are in the same data folder. I can open Discount.dbf from the local computer. So the problem seems to be related to the XLOOKUPC expression, over the network. It evaluates fine also. I'm using Alpha Four version 8 multi user.
Here is the full expression:
IF(R=0,0,IF(DISCOUNT="B".AND.R>55,100-((100-VAL(XLOOKUPC("DISCOUNT.DBF","DIS_RETA",STR(R*1.2),"DISCOUNT")))*1.2),IF(ISBLANK(DISCOUNT),VAL(XLOOKUPC("DISCOUNT.DBF","DIS_RETA",STR(R),"DISCOUNT")),0)))
Please help me solve this issue. Everything else seems to be working fine, but I am frustrated and wasted a lot of time trying many options to solve this. Thanks.

[Tom Cone Jr]

Suggest you check the helps for information on the isblank() function. In Alpha Five this function requires that the field name be passed into the function as character data. Is that true in Alpha Four?

If so, ISBLANK(Discount) should probably be ISBLANK("Discount").

It's a bit confusing to figure out your expression, the table seems to be named DISCOUNT, and the field you're checking with isBlank is also named Discount. So, take this suggestion with a grain of salt. Good luck.

[luthier]

In Alpha Four, isblank accepts any type of data, and returns logical. The manual shows examples of field names with and without the quotes, but I tried adding the quotes, and I still get the same error. Was worth a try, I thought you might have had it. You are right about both the database and the field in that database being named Discount.
I still think that the problem has something to do with the XLOOKUPC expression not working when from a remote computer over the network. I don't have this issue (Unable to find/create database: Discount) on the server computer.
I also wonder if the problem is A4 on the local computer not starting up with the correct data & script paths. When I start the server computer A4, I see the script path (-D#C:\Alpha4v8\Data) displayed in the brief opening screen. This does not display when I start A4 on the local computer, no matter what data path and script path I add to the A4 command. Perhaps someone could also advise on this, what command should I be using to start A4 on the local computer. My scripts and data are in C:\Alpha4V8\Data on the server. I have L drive mapped on the local computer to go to that same address.
This is the current command: C:\ALPHA4V8\UNWISE.EXE /W1 "C:\ALPHA4V8\INSTALL.LOG" -D#L:\ -N=L:\
I just tried A4 L:\ -D=L:\, and the L:\ -D=L:\ part now shows up on the local A4 start screen, which was hopeful, but it still does not have the correct script path (using Alt F3 and Set script path to check) and I still have the Discount.dbf error.
By the way, what is the purpose of the Search Path, shown under Script Path when setting the script path.
Thanks.

[Tom Cone Jr]

If this expression is being used in your field rules to populate a field with a discount rate, in A5 we'd call it a calculated field. It's possible latency over the network is preventing the calc from completing because you're asking the expression to do a lot of work. Each time xlookupc() is called Alpha has to find the table and open it. A new instance each time. Opening and closing tables are among the things that take the longest to complete, especially over a network. In the first part of the expression xlookupc() is used to open a table, search for a record, retrieve the field value, multiply it by 1.2, convert result to a string, pass it back, then convert the result to a number and subtract it from 100, and then subtract that result from 100. Presumably Alpha also closes the table, when the function has returned it's value to the larger expression. I'd look for a way to simplify this process, to speed it up. Or, look for a way to speed up the network. Good luck.

[luthier]

I get the error immediately as soon as I go from the main menu the first time to view a record, without making any change (typing C). So it seems to me that it would not be calculating yet? Is it possible for the discount rate field expression to run (calculate) just when you view a record? If not, why is the inventory database looking for the discount database when I view a record?

[Tom Cone Jr]

I don't know. Suggest you make the field user entered, temporarily. Does the system behave correctly? If so your connection to the server machine is intact. then test a simple calc field rule. Behave ok? then test a calc rule that looks up a field value, but doesn't do anything else. behave ok? then test a calc rule that looks up a value and does a simple calc with it. behave ok? .... continue testing, adding complexity til it breaks.

[luthier]

I tried removing the expression from the Disco_Rate field (no rules defined now), and still got the same Can't find Discount.dbf error on the local computer. Odd. So I checked all the other fields, and found one more (only) with a lookup to Discount.dbf. Aha. Removed that one also. Same error. So it appears that something else runs in the backround that checks for certain databases when you view a record. What could it be?

As a related note, I've noticed today that something looks for other databases to see if any are open when I try to create/edit field rules. If I am viewing a record in inventory on another computer, I can't access to edit field rules then until I close the other computer's view mode. Could this be similar to A4 looking for Discount.dbf when I view a record?

And why can't it find it anyway, it's in the same data path.

And is Search Path a possible option?

Also note that when I added the " " after the isblank command, that the calculation no longer worked properly for the discount rate field when editing or creating a record. It has to have no quote, at least when referring to a character field.

[Tom Cone Jr]

In A5 field rules can be defined only for tables (what you call databases... i.e. DBF files).

In A4 it's possible to define field rules for a Set. It's beginning to sound like you're working with rules defined for a set. Can you tell whether you're working with rules for a single database, or rules for a set? Next question: is the form you are using to view a record based on a single database or a set?

[luthier]

The database that is giving me trouble, INV.dbf (Inventory) is a single database, not a set. I have removed any expressions in this databases field rules that refer to Discount.dbf. Invoice is a set, which has a link parameter to Lineitem.dbf, which has a link paramenter to Inv.dbf. Nowhere in Invoice.set or lineitem.dbf do I see a reference to Discount.dbf, and Invoice.set works fine. But if I zoom to inventory, then I also get the Discount.dbf error.
My biggest question is why in the world can it not find Discount.dbf, when it is right there in the same data folder as all the other databases?

Update: I found several forms in INV.dbf that had calculated fields with Xlookupc, like this:
NET*((100-VAL((XLOOKUPC("\ALPHA4V7\DATA\DISCOUNT","DIS_RETA",STR(NET*2),"DISCOUNT"))))*.02)___________________
I changed all of those to "\ALPHA4V8\DATA\DISCOUNT", but still have the error. Even if it stayed with V7, there is still a folder and file with that address, so it still should be able to find it? Perhaps I have the expression written incorrectly? Or perhaps the expression is good enough to work on the server, but not good enough for use over the network? Should I add a "C:" to the beginning of the "ALPHA4V8\DATA\DISCOUNT"? Add a ".DBF" to the end? Should I try deleting these form field expressions temporarily also? (pain).

[luthier]

That line in the middle got broken up, hopefully this is easier to read:
NET*((100-VAL((XLOOKUPC("\ALPHA4V8\DATA\DISCOUNT","DIS_RETA",STR(NET*2),"DISCOUNT"))))*.02)

Another option would be to remove the \ALPHA4V8\DATA\ part of it?

[luthier]

When I removed the forms that had XLOOKUPC functions in the calculated fields, referring to Discount.dbf, it finally removed the problem. So that was definetly the problem. Now I just have to figure out how to write the expression correctly.

[luthier]

It was the "C:" that was missing before \ALPHA4V8\DATA\DISCOUNT that was the problem. It works ok on the home or server computer, but to work from a remote computer, it has to have the C: