Alpha Video Training
Page 3 of 3 FirstFirst 123
Results 61 to 82 of 82

Thread: Complex conditional lookup

  1. #61
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default Re: Complex conditional lookup

    G,
    contact me re: pancreas tumor. Your private is full.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  2. #62
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Complex conditional lookup

    Here you go.

  3. #63
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default Re: Complex conditional lookup

    For the heck of it, having followed this thread, I threw together another approach with a single "products" table holding both product and service with a field "type" to distinguish the two, an xdialog presentation of the products and services for selection, and scripting the additions to the invoice table instead of a table field lookup. It's another way.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  4. #64
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Complex conditional lookup

    Hi Gabe,

    Thanks for posting sample. Not sure what you have done here, but there is no lookup to choose between Residential and Commercial.

    Mike, Looks like the Itm_Lookup tables in the conditional lookup have gone AWOL. Either way, it seems that you are making the Invoice Items do two lookups. Is it not possible to do one lookup by getting the Customer type directly from the customer table?
    Last edited by Keith Hubert; 04-27-2009 at 04:04 PM.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  5. #65
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Complex conditional lookup

    Keith:
    There is no need for looking up anything. If you enter Commercial or residential and/or senior and enter the area, the correct price & senior discount will apply.

  6. #66
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default Re: Complex conditional lookup

    Keith,
    but there is no lookup to choose between Residential and Commercial.
    I believe that you are missing the concept that the customer type, commercial or residential, is not a specific, individual lookup or selection event. This is integral in the customer record with the field value for type, so once the customer is chosen, the customer type is set, and there is no lookup for customer type. Same for the condition of being senior.
    Either way, it seems that you are making the Invoice Items do two lookups. Is it not possible to do one lookup by getting the Customer type directly from the customer table?
    Again, it seems to me that you are mixing up apples and oranges. There are two conditions that need addressing, two selection events. 1-customer type. see above. 2-Product or Services. This is the selection event the user makes. Once the customer is selected, the customer type is set and doesn't need further address in the invoice. For the Item selection, I took the approach that there needs to be only one product table, so there in no need for 2 item tables, and so Item_lookup AND svc_lookup are combined into 1 table-products, similar to what Gabe did. Yes, the action of adding an item does generate 2 "lookups" (table.external_record_content_get() to generate the selection arrays for both product and services, but there is one user selection event. As long as the product table is not large, as I was reading that this application would not have, the array process works well.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  7. #67
    Member
    Real Name
    Scott Axton
    Join Date
    Dec 2001
    Location
    Colorado Springs CO
    Posts
    94

    Default Re: Complex conditional lookup

    Hi Everyone -

    Sorry to be so long in responding. Had to try and make a living today. This project is kind of a side for me.

    I will download a take a look at the updated examples.

    Thanks a lot for all your hard work.


    Mike - Based on your comments I think you have a good grasp of what I'm trying to do. Cant wait to look at what you've done.

    Thanks in advance.

    Scott

  8. #68
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Complex conditional lookup

    Hi Mike,
    I believe that you are missing the concept that the customer type, commercial or residential, is not a specific, individual lookup or selection event. This is integral in the customer record with the field value for type, so once the customer is chosen, the customer type is set, and there is no lookup for customer type.
    I have clearly understood that.
    For the Item selection, I took the approach that there needs to be only one product table, so there in no need for 2 item tables
    But the sample you posted has two tables in the conditional lookup, both of which are missing.

    OK, here is my take on the conditional lookup, which is what Scott asked about when he started this thread.

    I'm not sure if there is a bug or the error message is correct. If Customer is Commercial, Lookup works. If customer is Domestic, I get error message.

    When a customer is selected it is known what type they are (Domestic or Commercial). In my book there should not be a need to do another lookup in the Items browse to select the customer type to get the price structure.

    Either, all the prices for Domestic and Commercial are listed in one Product table, with a field to show each price as Domestic or Commercial, this table to be filtered in the lookup by the value of customer type. Or, two tables are used with conditional lookup, again the customer type determining which table to lookup.

    I would appreciate if you could advise why the conditional lookup is failing for Domestic customers.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  9. #69
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,572

    Default Re: Complex conditional lookup

    I don't think a conditional lookup is really needed here. Just a simple table lookup with a filter does the trick. Having 2 tables breaks some design principles, so put the Commerial and Residential records together and the table lookup on Product Id works.

    You also needed to set type v_type variable at startup - there was another error when the form opened for the first time.

    I don't know what was wrong with the conditional lookup... sometimes I feel that lots of messing around in this area causes problems. There didn't seem to be anything wrong, but deleting and re-entering the conditional lookups did not produce the error. But, doesn't look like a conditional lookup is required.

    Lastly, errors were produced on the Quantity column when an item was selected. Setting a default of 1 for Quantity, to be set when a new line item is created solves that one.

    In this database, the Commercial and Residential Products work, and the Service Area Cost works. But I'm still wondering about the Service stuff. More than one of Service will be offered? So the ServiceLookup table needs to change. Right now, when service is selected from the Products table, the Event code checks for "S" as the first letter of the Product. Then it goes and looks up the cost based on the Area. I changed the ServiceLookup table to include ResCost and CommCost.

  10. #70
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: Complex conditional lookup

    Keith:
    I didn'topen your zip file but do this so we could put this issue to rest:
    1-Go to the last zip file I attached
    2-Enter a new commercial client
    3-In the child table add a certain product and a service
    4-Check to see if the correct amounts were added for commercial
    5-Add a new residential client
    6-Add the same product and service to the residential client
    7-again check to see that the correct amounts were added and that they are not the same as if the client was a commercial one
    8-Add a new Senior Residential client
    9-Add the same product and service
    10-Check to make sure that the amount added for the service has a built-in a 5% discount.

    The amount is calculated based on:
    1-The Area
    2-Whether the client is Commercial vs Residential
    3-Whether the client is a senior or not

    All of the above could be accomplish using a different approach wherein if you enter a commercial client you look up in a certain table while if you enter a residential client you look up in a different one. However, that is not necessary here since the prices for commercial vs residential are all included in one table. When I asked you were these values are, you pointed me to the Product table.
    There is no need for any tables to popup except when the user is adding Products/Service. Once the user enters those, the price is calculated based on all the above factors. There is no need for the user to choose a price from a lookup table. All the user has to do is enter these different peices of information and the price is calculated by alpha.

  11. #71
    Member
    Real Name
    Scott Axton
    Join Date
    Dec 2001
    Location
    Colorado Springs CO
    Posts
    94

    Default Re: Complex conditional lookup

    I'm really not ignoring everyone - I swear! I've just got a moment to reply here. It is looking like I wont be able to do any more work on this til this weekend.

    I'll look at these and get back to you all ASAP.

    Thanks,

    Scott

  12. #72
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Complex conditional lookup

    Hi David,

    Thanks for sorting out my variable problem. I can now do the conditional lookup just the same as doing a filtered lookup.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  13. #73
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default Re: Complex conditional lookup

    Keith,
    Yes, you were completely correct, excuse me. The table had all those conditional lookups that were not active still there from duplicating the example table. I got rid of all those. Here is my final effort after correcting all the issues. I am one who doesn't like the practice of entering data into a browse object. This is why I take the approach of entering the data in dialogs and filtering tables and writing to the tables through scripts.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  14. #74
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Complex conditional lookup

    Mike,

    As always a beautiful job. There is so much to learn from your examples.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  15. #75
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,572

    Default Re: Complex conditional lookup

    Very nice Mike... I wonder if I'll ever be able to write dialogs that well... I've may start changing my approach to this stuff and using dialogs more.

  16. #76
    Member
    Real Name
    Scott Axton
    Join Date
    Dec 2001
    Location
    Colorado Springs CO
    Posts
    94

    Default Re: Complex conditional lookup

    Mike -

    This is incredible. It does just what I wanted. I have to really pull things apart to see how I can fit it in with the over all scheme of the rest of what I've done. Can't have one great looking piece and the rest only mediocre don't you know. Back to the drawing board and the books to see what I can learn.



    Everyone -

    You guys have all been great - I really appreciate all the comments and banter back and forth. It has served to show me several things.
    1. I have a lot of learning to do.

    2. There are about 6 ways from Sunday in the way you can accomplish something in Alpha 5.

    3. The Alpha Community is the best - bar none - when it comes to helping each other out. I only hope I can get to the point where I can start contributing answers instead of just asking questions.

  17. #77
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Complex conditional lookup

    That's a nice implementation, Mike. I actually like this one better than some of your other ones - even better than your airplane one (although I loved the plane graphics). Clean interface comes to mind. One minor point, though. You have middle name required. Some people don't have 'em, some people don't like to use 'em. But nice job.

  18. #78
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default Re: Complex conditional lookup

    Quote Originally Posted by Peter.Greulich View Post
    That's a nice implementation, Mike. I actually like this one better than some of your other ones - even better than your airplane one (although I loved the plane graphics). Clean interface comes to mind. One minor point, though. You have middle name required. Some people don't have 'em, some people don't like to use 'em. But nice job.

    Thanks!
    Yeah. It's a passive aggressive thing with me. Mike is my middle name. I don't use my first name. And actually I work with 2 others that do the same. In fact, I believe the figure is about 4-6% of men don't use their first names. I awefully bestowed that upon my son, too. In all my applications that have customers, clients, contacts, staff, etc, I aways have a fourth field for a common name/nickname, so the formal name and the common names are achievable.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  19. #79
    Member
    Real Name
    john linley
    Join Date
    Aug 2007
    Location
    englewood cliffs, nj
    Posts
    133

    Default Re: Complex conditional lookup

    Similar Headache?

    I would like to enter one of 12 values automatically, rather than via a lookup!

    The twelve values are bank month codes CH01, CH02, CH03....TO....CH12.

    CHO1 Would be deposits or withdrawals occurring in the month of January
    CH12 would be deposits or withdrawals occurring in the month of December

    How might I automatically insert a bank code value in the bank code field based on a pre-entered date value in either a deposit date or a payment date field ???

    Bank code field cannot be an unchangeable calculated value.
    For example "CH03" may 'later' need to be changed to "CH04", if a check written in March clears the bank in April.


    John Linley

  20. #80
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,168

    Default Re: Complex conditional lookup

    How about a form OnSave event with:
    Code:
    parentform:bank_month_code.value ="CN"+left(convert_type(parentform:datefield.value,"c"),2)
    in red would be what your field names are.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  21. #81
    Member
    Real Name
    john linley
    Join Date
    Aug 2007
    Location
    englewood cliffs, nj
    Posts
    133

    Default Re: Complex conditional lookup

    Keith:

    A different lookup question. Is it possible to automatically post (insert) a value based on a date? I have a checking account set which collects deposits and withdrawals from other tables. On the withdrawal side, when entering check data prior to printing the checks, a table lookup offers twelve bank codes, one for each month or checking period. The codes are "CH01" FOR Jan TO "CH12" FOR Dec. When entering the check date, Say today: { 08/10/2009}, I would like an automatic entry into the bank code field "CH08". What I have now is a lookup which displays the twelve bank codes, and we have to scroll to August.

    If someone has two or three separate bank accounts, the scroll list could be 24 or 36 instead of 12.

    I would need it not to be a calculated field, as it might have to be changed at a later date. For example, if a check previously assigned the August code "CH08" but does not clear the bank until Sep, the bank code would need to be changed to "CH09".

    Hope you notice this post and can help.

    Regards,

    John LInley

  22. #82
    Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,629

    Default Re: Complex conditional lookup

    Quote Originally Posted by john linley View Post
    I would need it not to be a calculated field, as it might have to be changed at a later date.
    Not sure, but it could be a calc field. If, for example, you have a date field called cleared_date, and the calc field based upon that field, the calc field would update whenever you change the cleared_date field. Otherwise, with xbasic you can do anything that you need to.

Similar Threads

  1. Complex Conditional Statements
    By Ronald Dodge in forum Alpha Five Version 5
    Replies: 19
    Last Post: 05-23-2004, 09:09 AM
  2. Conditional Lookup
    By pinzgauer in forum Alpha Five Version 5
    Replies: 3
    Last Post: 12-21-2003, 02:56 PM
  3. complex lookup table
    By Pat in forum Alpha Five Version 5
    Replies: 4
    Last Post: 11-18-2003, 07:03 AM
  4. Need help creating complex lookup expression
    By joannristau in forum Alpha Five Version 5
    Replies: 12
    Last Post: 05-02-2003, 02:17 AM
  5. complex lookup
    By bob muzik in forum Alpha Five Version 4
    Replies: 14
    Last Post: 12-17-2000, 06:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •