Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook



The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic

Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.

If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.

Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.

Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

field rule that uses XLOOKUPC

  • Filter
  • Time
  • Show
Clear All
new posts

    field rule that uses XLOOKUPC

    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:
    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.

    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.


      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.


        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.


          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?


            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.


              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.


                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?


                  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:
                  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).


                    That line in the middle got broken up, hopefully this is easier to read:

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


                      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.


                        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: