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
Devcon 2020 Goes Virtual! LEARN MORE >

Announcement

Collapse

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

conditional lookup problems

Collapse
X
Collapse
 
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
    #1

    conditional lookup problems

    This is a problem that I was having trouble with last spring, solved to a workable degree, but now its back.

    I have a table with the following fields:
    Dkt_Num,Character,8 (user entered - no auto increment)
    Rate,Character,1 (entered from simple list w/ choice of "2" or "Y")
    Op_Type,Character,3 (entered from simple list w/ choice of MR, WUP or RUN)
    *Oper_Id,Character,8 (filled in by lookup from related table of press operations)
    *Descrip,Character,30(filled in by lookup from related table of press operations)
    Press_Notes,Memo,10 (user entered)
    Emp_Id,Character,6 (filled in by lookup from employees table)
    Chits,Numeric,6,0 (user entered)
    Total_Pcs,Numeric,8,0(user entered)
    Pcs_Perhr,Numeric,8,0(calc'ed - (60/(CHITS*6))*TOTAL_PCS)
    *Cost,Numeric,10,2 (conditional fill with value from related table of press ops)
    (the 3 fields with *'s are filled in one auto popup lookup when the user gets to the Oper_ID field and makes a choice of a press operation)

    Here are the 8 lines from the Source/Fill/Display/Width/Condition browse in the Field Rules dialog that determines what values get put into the Oper_ID, Descrip & Cost fields:
    PRESS_OPS->Oper_Id OPER_ID Yes 6
    PRESS_OPS->Oper_Descript DESCRIP Yes 20
    PRESS_OPS->Mr_Rate_2 Cost Yes 10 Rate="2".AND.Op_Type="MR"
    PRESS_OPS->Mr_Rate_Y Cost Yes 10 Rate="Y".AND.Op_Type="MR"
    PRESS_OPS->Wup_Rate_2 Cost Yes 10 Rate="2".AND.Op_Type="WUP"
    PRESS_OPS->Wup_Rate_Y Cost Yes 10 Rate="Y".AND.Op_Type="WUP"
    PRESS_OPS->Run_Rate_2 Cost Yes 10 Rate="2".AND.Op_Type="RUN"
    PRESS_OPS->Run_Rate_Y Cost Yes 10 Rate="2".AND.Op_Type="RUN"

    And here is a sample of what is in the Press_Ops->*_Rate_* fields:
    Oper_Id/Oper_Descript/Mr_Rate_2/Mr_Rate_Y/Wup_Rate_2/Wup_Rate_Y/Run_Rate_2/Run_Rate_Y
    PR-0010,40' 2-Color/1 person: Cover,15.55,12.50,15.55,12.50,15.55,12.50

    The last field - Cost - is the problem as I do not get consistent results. If any of the first 4 conditions are met it works correctly. The last 2 are always wrong:
    - When Rate=2 & Op_Type=RUN the value from the Run-Rate-Y field gets filled in
    - When Rate=Y & Op_Type=RUN a value of 0 is filled in.

    Other info: Version A5V4.5 Build 266. The problem occurs when I'm working on the app at my office, when I demo it to my customer using my laptop, and from an unlimited runtime of this app on the customer's systems.

    Any ideas? - and thanks, I really appreciate this forum and the help that gets passed out here.
    Tags: None
    #2
    RE: conditional lookup problems

    Are you familiar with xbasic, dialog forms, and/or arrays? I use these methods rather than the lookups for anything as complex as you describe.
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    [email protected]

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

    Comment

      #3
      RE: conditional lookup problems

      I don't know enough xBasic to do this. Besides, that would seem to add far more complexity to the app than what looks to me like a fairly simple conditional lookup problem:

      A user working in an embedded browse table fills in a field named "RATE" with either a "2" or "Y" value, goes to the next field in the line named "Op_Type" and fills it in with a value of either "MR," "WUP" or "RUN," then in the next field named "Oper_ID" where an auto popup lookup table with 6 columns/fields (ID, Desc, MR_Rate_2, MR_Rate_Y, WUP_Rate_Y, etc.) opens, a line item/record gets selected and the info from this line item then populates/fills in 3 fields (Oper_ID, Descrip, Cost) in the "master" table. And the conditions are simple logic - if RATE=2 AND OP_Type=MR then use the value in the MR_Rate_2 field from the lookup table to fill in the cost field, etc.

      It works 4 out of 6 times, it only fails when one of the conditions involves a value called "RUN" and it fails in 2 distinct ways - it either puts in a value from the wrong field (eg, puts in value from RUN_Rate_Y when it should have come from RUN-Rate-2) or it puts in a 0 value.

      I had tried a little more complexity at first -- RATE="2".AND.Oper_Type="WUP".OR.Oper_Type="RUN" since WUP & RUN rates are the same -- but this also had inconsistent results so my current version is an attempt to simplify the conditions.

      Since this is a working app I really can't just try a drastic restructuing as a fix so I slowly work away at this and a several other bugs the users complain about - fortunately the 2 users who enter the job-costing data know prices so well they just overwrite the incorrect data that the conditional loopup plugs in, but they want it to work correctly, so... here's what I'm wondering about:
      - is RUN a restricted word? (I thought the later versions of A5 don't have this prob.)
      - is there a limit of how many conditions can be used in one lookup/fill (the first 4 of the 6 work correctly)
      - is there a problem with the naming of a field "Oper_ID" in the embedded browse table (Press_Cost.dbf) when there is also a field of that name in the table used for the lookup (Press_Ops.dbf) and they're not key fields? Note that Press_Costs is the parent to the Press_Ops table with a one->many link.

      Comment

        #4
        RE: conditional lookup problems

        Bill,
        It works 4 out of 6 times, it only fails when one of the conditions involves a value called "RUN"

        A few thoughts:

        Does it fail every time the value is "RUN"??
        Does it fail only when the value is "RUN"??

        It sounds to me like your lookup table has several fields that could populate a single field ( cost )in the master table.Are the conditions that determine this mutually exclusive for every field in the lookup table dumping into cost? Is there any way Alpha could legitimately think that more than one condition is true for several fields in the master table and get confused?

        If it is an Alpha quirk, did you try altering the condition a little to see if it changes this behavior:
        substr(field,1,2) = "RU" or
        inlist("RUN",field)
        Sounds a little silly to try the above, but worth a try.

        Is it a monstrous change to use another value other than "RUN" in that field.

        John

        Comment

          #5
          RE: conditional lookup problems

          Bill,

          After loooking back at your conditions in the first message:
          The last two conditions appear to be identical to me. If that's not a typo and my eyes aren't failing me, that could be the problem. They both appear to have the same condition :
          Rate="2".AND.Op_Type="RUN"

          John

          Comment

            #6
            RE: conditional lookup problems

            John,

            Thanks for your good proofreading - it is a typo only here. The last two conditions should read:
            Rate="2".AND.Op_Type="RUN"
            Rate="Y".AND.Op_Type="RUN"

            Comment

              #7
              RE: conditional lookup problems

              I'm sending some conditional lookup tests that I've just put together that duplicates just the 2 tables from my jobcost app that have been causing the problem. The first lookup test works correctly when any of the 6 conditions are met so this test does NOT duplicate the problem I'm having in the real app but I think it shows how really simple this is.

              The 2nd test is an attempt to consolidate the lookup fields from 6 to 4 columns/fields since the WUP and RUN prices are always the same. The changes to this 2nd test are this:
              - deleted the 2 WUP_Rate_* fields from the Press_Ops table
              - deleted the 2 WUP fill lines as conditions for lookups
              - changed the logic in the 2 RUN fill lines conditions to...
              * Rate="2".AND.Op_Type="WUP".OR.Op_Type="RUN"
              * Rate="Y".AND.Op_Type="WUP".OR.Op_Type="RUN"

              The 2nd test does not work correctly when the Rate=2 and the Op_type=RUN - instead of getting the price from the Press_Ops.dbf > Run_Rate_2 column it gets it from the Run_Rate_Y column (which is the last column in the record). When the Rate=Y and the Op_Type=RUN it "appears" to work correctly but that value is coming from the last column in the record so I'm not sure about this until I tinker with prices I put in these 4 different rate fields.

              I'm pointing out this last column stuff because last spring when I was first writing this app and didn't have the conditional syntax correct, all of the values that did get filled in were from the last column of whatever line item/record got selected from the Press_Ops table so I've come to a conclusion that whenever a conditional expression can't be evaluated correctly by A5 it just plugs in whatever value is in the last field/column of the record.

              As for using these tests I just use the default form from Press_Costs for entering data and just open the tables in browse mode to compare all of the records.

              Thanks again for your help with this.

              Comment

                #8
                RE: conditional lookup problems

                Bill,
                Your conditions with the or should be changed to look like the following.

                RATE="2".AND. (OP_TYPE="WUP".OR.OP_TYPE="RUN")
                instead of
                RATE="2".AND. OP_TYPE="WUP".OR.OP_TYPE="RUN"

                The parentheses are important because Alpha wants to evaluate the AND first.

                John

                Comment

                  #9
                  RE: conditional lookup problems

                  Thanks John,

                  The parenthesis fixed my 2nd conditional lookup test. I then went into my app and made the following changes:
                  - deleted the 2 WUP_Rate_* (2 & Y) fields from the Press_Ops.dbf file
                  - deleted the 2 WUP fill/lookup lines in the Press_Costs.dbf field rules
                  - edited the conditions in the Press_Costs.dbf field rules so they now read as:
                  RATE="2".AND.(OP_TYPE="WUP".OR.OP_TYPE="RUN")
                  instead of
                  RATE="2".AND.OP_TYPE="RUN"

                  and

                  RATE="Y".AND.(OP_TYPE="WUP".OR.OP_TYPE="RUN")
                  instead of
                  RATE="Y".AND.OP_TYPE="RUN"

                  but the same problem continues when the conditions 2 & Run are true - the price is fetched from the Run_Rate_Y column rather than from Run_Rate_2.

                  Hmmmmmmm!

                  Comment

                    #10
                    RE: conditional lookup problems

                    John - another THANK YOU! your fix of using parenthesis did fix my problems after I got rid of a 2nd instance of my incorrect conditions. I had the same conditions in the cost field and just disabling it didn't fix it. But after editing the conditions in the Oper_id field with your fix, then re-enabling the conditional lookups from the cost field and deleting them and again disabling lookups from cost, everything works correctly. Thank you, thank you, thank you!!! Not sure what I was thinking when I setup essentially the same lookup from 2 different fields. Dumb Da DumDum.

                    Comment

                    Previous template Next
                    Working...
                    X