Alpha Video Training
Results 1 to 25 of 25

Thread: Conditional Table Lookup From selection?

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

    Default Conditional Table Lookup From selection?

    Hi Guys,

    I'm having a problem when trying to add a new record to a table using a specified form which is linked to the selected table.

    The lookup works and shows the correct data in the default browse. It is when I click on Add new record from the default browse that I get the problem. It looks like the field rules is not holding the info. It tells me that it cant find the form, which in fact belongs to another table and opens up the default form instead.

    Just so I don't spend a lot of time wondering why this is this not working as desired, is this a known problem?
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

  2. #2
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Conditional Table Lookup From selection?

    Hi Keith
    You want to add a new record to a table using a form linked to the table - good so far
    Wherefrom cometh the lookup and default browse that you click to Add a New record?
    Generally, v10 has never, for me displayed a problem adding a record to a table from a linked form or browse in any way. Unless of course there may be a field rule conflict.

  3. #3
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,365

    Default Re: Conditional Table Lookup From selection?

    Suggest you put the table with the form you want into the set.
    Link on a dummy field if you need to.
    Possible alternative. Use an explicit path for the form you want to open.
    How come you are back on V10.5 Keith?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

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

    Default Re: Conditional Table Lookup From selection?

    Hi Ted,

    This is not a SET. The form is built on the table I want to add the new record to.

    Each table in the conditional list has it's own form.

    Reason for V10, big project started some time ago.
    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. #5
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Conditional Table Lookup From selection?

    Ray,

    I dont have problems with adding new records to a table from a lookup browse.

    The problem seems to be because the browse is from a conditional table 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!

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Conditional Table Lookup From selection?

    As always, this would be much easier to diagnose and investigate with a sample database exhibiting the problem than it is guessing what you have. It doesnt have to be a sample of the database you have distributed, just a small database that misbehaves in the way you describe.
    There can be only one.

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

    Default Re: Conditional Table Lookup From selection?

    Good idea Stan,

    I shall knock one up. Chances are I cant duplicate the problem. Lets give it a go.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

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

    Default Re: Conditional Table Lookup From selection?

    OK Here is sample from application.

    As mentioned the problem seems to be that the selected form for each table is not being kept in field rules.

    TIA
    Attached Files Attached Files
    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. #9
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,365

    Default Re: Conditional Table Lookup From selection?

    I tried several alternatives Keith.
    It worked after a fashion in V11, but not very well. I found that the tables were not getting saved properly. One in test would display the for I specified, the other would not.
    Going back to the Field Rules, the table name was corrupt.
    Only other thing I can think of is creating and Event in the Field Rules to fire a lookup/edit on a specific table.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  10. #10
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Conditional Table Lookup From selection?

    Agreed, given my limited experience with lookups of this type I constructed something similar and encountered the same issue. At best only one form name is saved and when the lookup is used it generally fails if one is specified.
    There can be only one.

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

    Default Re: Conditional Table Lookup From selection?

    Conclusion this is a bug?
    Only other thing I can think of is creating and Event in the Field Rules to fire a lookup/edit on a specific table.
    Ted, how do you suggest I do that?
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

  12. #12
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Conditional Table Lookup From selection?

    Conclusion this is a bug?
    Reality, it doesn't matter. Probably is but a waste of time to report it.
    There can be only one.

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

    Default Re: Conditional Table Lookup From selection?

    Sent as Bug report for V12.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

  14. #14
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,365

    Default Re: Conditional Table Lookup From selection?

    You'll be lucky!
    Try putting an event in the field you want a lookup for.
    If the trigger is "this data open that form" , make the link to the form.
    Change the data or addit, and capture the data as a variable.

    I have had an issue recently when mid data entry, client wanted to add a new member because the one they wanted was not in the lookup.
    My workaround was a Modal form into which they could add the data and return to data input. The original form is suspended, and once the modal one is saved, the data becomes available.
    Hope this helps.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

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

    Default Re: Conditional Table Lookup From selection?

    Hi Ted,
    Hope this helps
    It might if you could show me in the sample I posted.
    Last edited by Keith Hubert; 03-27-2014 at 01:42 AM.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

  16. #16
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,365

    Default Re: Conditional Table Lookup From selection?

    I will try tomorrow, Keith.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  17. #17
    "Certified" Alphaholic
    Real Name
    John Koh
    Join Date
    Jan 2004
    Location
    Maryland, USA
    Posts
    1,082

    Default Re: Conditional Table Lookup From selection?

    Keith,

    After I made a new form, The "Sm_fab_code" field is working fine with v9. Check your code in the close button. I am not able to close this form after edit.

  18. #18
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: Conditional Table Lookup From selection?

    Like that Ted. I'll borrow the idea (give it back when finished - promise). Simple, obvious and elegant.
    Keith, Ted will probably be more helpful than I and give you an example.
    It requires a trigger (a simple button even) on the form to open the modal enter new member form.

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

    Default Re: Conditional Table Lookup From selection?

    Here is updated sample to focus on problem. Which is, unable to save correct forms in field rules when using Conditional Table Lookup.
    Attached Files Attached Files
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

  20. #20
    "Certified" Alphaholic
    Real Name
    John Koh
    Join Date
    Jan 2004
    Location
    Maryland, USA
    Posts
    1,082

    Default Re: Conditional Table Lookup From selection?

    Keith,

    The drop down boxes are working fine except "Other" class which did not set the lookup condition.

    Just my idea, If you have an extra field for fabric type, then you may have one table instead of many product tables.

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

    Default Re: Conditional Table Lookup From selection?

    John,

    Thanks for your input. I have heard back from Alpha and they tell me that you can't have a form for each table in Conditional Table Lookup.

    This strikes me as odd, if the user needs to add a record on the fly the same as you can with a single table.

    Because of this limitation, I have gone down the single table route and added in some cleaver xbasic to get values from another table and the user can update the table with Xdialog.
    Code:
    'Create an XDialog dialog box to prompt for parameters.
    DIM SHARED vc_class as C
    DIM SHARED vc_description as C
    DIM SHARED varC_result as C
    auto_list_vc_class = table.external_record_content_get("fabric_class","Classification","classification","")
    
    temp_count = w_count(auto_list_vc_class,crlf())
    DELETE a_vc_class
    DIM a_vc_class[temp_count] as c
    a_vc_class.initialize(auto_list_vc_class)
    ok_button_label = "&OK"
    cancel_button_label = "&Cancel"
    varC_result = ui_dlg_box("Classifaction Selector",<<%dlg%
    {region}
    Select Clssification:| [.40,5vc_class^#a_vc_class];
    Enter Description:| [.100vc_description];
    {endregion};
    {line=1,0};
    {region}
    <*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
    {endregion};
    %dlg%)
    
    'Conditional code follows. Executes only if the condition expression is True.
    IF a5_eval_expression("=Var->varC_result=\"Cancel\"",local_variables()) THEN 
    'End the current script
    END
    
    END IF
    
    'Execute inline Xbasic code.
    dim vc_code as c
    'debug(1)
    dim global vc_class_no as n
    vc_class_no=lookup("Fabric_Class","Classification= "+s_quote(vc_class) ,"Lastused")
    'debug(1)
    dim tblb as p
    tblb = table.open("fabric_class",FILE_RW_EXCLUSIVE)
    tblb.query_detach_all()
    query.description = "Classificationn"
    query.order = "Classification"
    query.filter = "Classification= "+s_quote(vc_class)
    query.options = "D"
    tblb.query_create()
    lastnum = vc_class_no
    nextnum = lastnum+1
    tblb.change_begin()
    tblb.Lastused = nextnum
    tblb.change_end(.t.)
    tblb.close()
    vc_code=nextnum
    'nextnum=nextnum+vc_style_no
    tbla = table.open("Fabric_codes")
    tbla.enter_begin(.t.)
    tbla.sm_fab_code = left(vc_class,1)+alltrim(str(var->vc_code))
    tbla.fabric_description=vc_description
    tbla.enter_end(.t.)
    
    'Displays a message box (style:'OK Button', image: 'Information symbol', id: 'msg2').
    DIM SHARED  msg2_result as N
    DIM SHARED  msg2_OK_Button as L 
    msg2_OK_Button = .F.
    
    title_var = "SM Fabric Code"
    'Message is an expression. Need to evaluate the expression in the context of the current form.
    message_text_var = a5_eval_expression("=\"You have saved \"+left(Var->vc_class,1)+Var->vc_code+crlf(1)+Var->vc_description",local_variables())
    msg2_result=ui_msg_box(title_var,message_text_var,UI_OK+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
    'Test to see which button on the message box was pressed....
    SELECT
    	CASE msg2_result = UI_OK_SELECTED
    		msg2_OK_Button= .t.
    END SELECT
    f
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

  22. #22
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,365

    Default Re: Conditional Table Lookup From selection?

    So are you sorted now Keith?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

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

    Default Re: Conditional Table Lookup From selection?

    So are you sorted now Keith?
    Yes thanks Ted, but in a way better than I was trying with multiple tables.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

  24. #24
    Member
    Real Name
    John Michaels
    Join Date
    May 2013
    Posts
    54

    Default Re: Conditional Table Lookup From selection?

    Keith,
    I looked at the code you posted. Looks like Action Scripting. The variable vc_code is dimmed as character in line 29 and it seems to be trying to acquire a numeric value in line 48. Not sure how that is working for you.
    John Michaels
    "The only thing needed for evil to flourish is for good men to do nothing" - great quote but probably not Sir Edmund Burke

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

    Default Re: Conditional Table Lookup From selection?

    Hi John,

    Thanks for noticing that difference, I will take another look at my code.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


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

Similar Threads

  1. Conditional Table Lookup
    By khebert in forum Alpha Five Version 7
    Replies: 3
    Last Post: 11-16-2006, 02:57 PM
  2. Table lookup shows wrong selection on enter
    By BuzzB in forum Alpha Five Version 7
    Replies: 2
    Last Post: 08-04-2006, 04:16 PM
  3. table lookup fails to retain correct selection
    By BuzzB in forum Alpha Five Version 7
    Replies: 7
    Last Post: 07-22-2006, 02:54 PM
  4. Conditional Table lookup
    By paulb in forum Alpha Five Version 5
    Replies: 9
    Last Post: 02-12-2003, 04:30 PM
  5. Conditional table lookup
    By sheila morales in forum Alpha Five Version 4
    Replies: 3
    Last Post: 05-18-2002, 11:11 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
  •