Exlusive boolean

jimmyf
jimmyf Member Posts: 104
edited 2009-07-08 in NAV/Navision Classic Client
I have put a boolean flag on the Ship to Address table called Principal Address
The business logic is that there can only be one Principal Address per customer account so when a user places a tick in tnis field the previous flag is set to false.

This is my code which does not work! Any help would be appreciated..


Principal Address - OnValidate()
recShipToAddress.RESET;

recShipToAddress.SETRANGE(recShipToAddress."Customer No.","Customer No.");
recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);

IF recShipToAddress.FINDSET THEN BEGIN
recShipToAddress.MODIFYALL(recShipToAddress."Principal Address",FALSE);
END;

Comments

  • gerdhuebner
    gerdhuebner Member Posts: 155
    Perhaps you should only run this code, if the user actually checks the box...
    At the moment, the code is also run, if the user unchecks the field.
    New kits on the blog: https://massivedynamicsblog.wordpress.com
  • Savatage
    Savatage Member Posts: 7,142
    What are you tring to have happen.

    I'm assuming, if you want to set a ship-to as primary and another ship-to is already the primary,
    that you want your code to check the new account and go thru the others unchecking it?

    is this what you're tring to do?

    what's the point on filtering the code?
    recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    Try this code: 
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE(recShipToAddress."Customer No.","Customer No.");
  recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);
  IF recShipToAddress.FINDFIRST THEN BEGIN
    recShipToAddress."Principal Address" := FALSE;
    recShipToAddress.MODIFY;
  END;
END;
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • Savatage
    Savatage Member Posts: 7,142
    I was also thinking of adding a confirm..
    that would give you a message and which code it finds.

    "Primary Address Already exists for this customer: Code %1\
    Do you want to change it?"

    We have an account with 215 ship-to's, if we did do something like this I would like to know which one is changing.
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • jimmyf
    jimmyf Member Posts: 104
    Savatage wrote:
    What are you tring to have happen.

    I'm assuming, if you want to set a ship-to as primary and another ship-to is already the primary,
    that you want your code to check the new account and go thru the others unchecking it?

    is this what you're tring to do?

    what's the point on filtering the code?
    recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);

    This is exactly what I am trying to do!
  • jimmyf
    jimmyf Member Posts: 104
    Luc Van Dyck wrote:
    Try this code: 
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE(recShipToAddress."Customer No.","Customer No.");
  recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);
  IF recShipToAddress.FINDFIRST THEN BEGIN
    recShipToAddress."Principal Address" := FALSE;
    recShipToAddress.MODIFY;
  END;
END;


    This does not work I am afraid! Thanks for trying :)
  • jimmyf
    jimmyf Member Posts: 104
    Savatage wrote:
    What are you tring to have happen.

    I'm assuming, if you want to set a ship-to as primary and another ship-to is already the primary,
    that you want your code to check the new account and go thru the others unchecking it?

    is this what you're tring to do?

    what's the point on filtering the code?
    recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);

    This is to exclude the record that I have checked from being unchecked.
  • reijermolenaar
    reijermolenaar Member Posts: 256
    Hi Jimmy,

    The code of Luc is ok so there is something strange happening.
    Do you have accidentally added a local variable called "Customer No."?

    What happens if you add a message like this: 
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE("Customer No.", "Customer No.");
  recShipToAddress.SETFILTER(Code, '<>%1', Code);
  ERROR(recShipToAddress.GETFILTERS);
END;
    Reijer Molenaar
    Object Manager
  • jimmyf
    jimmyf Member Posts: 104
    reijermolenaar wrote:
    Hi Jimmy,

    The code of Luc is ok so there is something strange happening.
    Do you have accidentally added a local variable called "Customer No."?

    What happens if you add a message like this: 
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE("Customer No.", "Customer No.");
  recShipToAddress.SETFILTER(Code, '<>%1', Code);
  ERROR(recShipToAddress.GETFILTERS);
END;

    The message displays the appropriate Customer No and Code for the record selected!
    I have prototyped Lucs code on a V5SP1 Cronus demo database. There are no local variables. I have placed the code on the on validate trigger.
    Still does not work!!!
  • matttrax
    matttrax Member Posts: 2,309
    I've done this countless times and the code you have is very similar to what I've always done.

    //To prevent execution when you validate TRUE on one that was already TRUE, I check the previous value as well
    //Prevents extra updates from being sent to the database
    IF (BooleanField) AND (NOT xrec.BooleanField) THEN BEGIN
    localRec.SETRANGE("Customer No.", rec."Customer No."); //Filter 1 to get only this customer's addresses
    localRec.SEFILTER(Code, '<>%1', rec.Code); //Filter 2 to eliminate this record that was just checked
    localRec.SETRANGE(BooleanField, TRUE); //Filter 3, to find the one record that is already checked
    localRec.MODIFYALL(BooleanField, FALSE);

    //Or use this code, as there should only be one other box checked anyway so no need for a repeat and changing a value
    //that has been filtered on (throws off the NEXT)
    //IF localRec.FINDFIRST THEN BEGIN
    // localRec.BooleanField := FALSE;
    // localRec.MODIFY;
    //END;
    END;
    Matt Traxinger - ArcherPoint
  • jversusj
    jversusj Member Posts: 489
    jimmyf wrote:
    reijermolenaar wrote:
    Hi Jimmy,

    The code of Luc is ok so there is something strange happening.
    Do you have accidentally added a local variable called "Customer No."?

    What happens if you add a message like this: 
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE("Customer No.", "Customer No.");
  recShipToAddress.SETFILTER(Code, '<>%1', Code);
  ERROR(recShipToAddress.GETFILTERS);
END;

    The message displays the appropriate Customer No and Code for the record selected!
    I have prototyped Lucs code on a V5SP1 Cronus demo database. There are no local variables. I have placed the code on the on validate trigger.
    Still does not work!!!
    when you say this still does not work, what do you mean? after this code executes are you finding other ship-tos for this customer where this boolean is true, or are you finding no ship-tos where this is true? when you say you are doing this OnValidate - is that at the table or form level (just looking for more information on why this might be failing)?
    kind of fell into this...
  • jimmyf
    jimmyf Member Posts: 104
    jversusj wrote:
    jimmyf wrote:
    reijermolenaar wrote:
    Hi Jimmy,

    The code of Luc is ok so there is something strange happening.
    Do you have accidentally added a local variable called "Customer No."?

    What happens if you add a message like this: 
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE("Customer No.", "Customer No.");
  recShipToAddress.SETFILTER(Code, '<>%1', Code);
  ERROR(recShipToAddress.GETFILTERS);
END;

    The message displays the appropriate Customer No and Code for the record selected!
    I have prototyped Lucs code on a V5SP1 Cronus demo database. There are no local variables. I have placed the code on the on validate trigger.
    Still does not work!!!
    when you say this still does not work, what do you mean? after this code executes are you finding other ship-tos for this customer where this boolean is true, or are you finding no ship-tos where this is true? when you say you are doing this OnValidate - is that at the table or form level (just looking for more information on why this might be failing)?

    When the code executes there are still other ship to records for this customer that are still true! I am executing the code at table level on the on validate trigger of the Primary Address field.
  • jimmyf
    jimmyf Member Posts: 104
    Thanks to eveyone for your help, I got it to work.
    A big thanks to Mattrax

    Here is my final code..

    This code is executed on the on validate trigger of my custom "Principal Address" Field

    IF ("Principal Address") AND (NOT xRec."Principal Address") THEN BEGIN
    recShipToAddress.RESET;

    recShipToAddress.SETRANGE(recShipToAddress."Customer No.","Customer No.");
    recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);
    recShipToAddress.SETRANGE(recShipToAddress."Principal Address",TRUE);

    IF recShipToAddress.FINDSET THEN BEGIN
    recShipToAddress.MODIFYALL(recShipToAddress."Principal Address",FALSE);
    END;
    END;

    I added this field to the Ship-To Address List form and added..

    Principal Address - OnAfterValidate()
    CurrForm.UPDATE;
  • garak
    garak Member Posts: 3,263
    u dosn't need the findset. Normally it can give only one rec with the flag. and on this rec you remove the flag

    recShipToAddress.RESET;
    recShipToAddress.SETRANGE("Customer No.","Customer No.");
    recShipToAddress.SETFILTER(Code,'<>%1',Code);
    recShipToAddress.SETRANGE("Principal Address",TRUE);
    if recShipToAddress.findfirst then begin
    recShipToAddress.locktable; //to say the sql server, now we will modify the rec
    recShipToAddress."Principal Address" := FALSE;
    recShipToAddress.modify;
    end;

    if you really want to use the modifyall statement, the use this:

    recShipToAddress.RESET;
    recShipToAddress.SETRANGE("Customer No.","Customer No.");
    recShipToAddress.SETFILTER(Code,'<>%1',Code);
    recShipToAddress.SETRANGE("Principal Address",TRUE);
    if not recShipToAddress.isempty then
    recShipToAddress.modifyall("Principal Address",FALSE);

    Here a link to the blog of WALDO with a nice article.

    http://dynamicsuser.net/blogs/waldo/arc ... teall.aspx

    Regards
    Do you make it right, it works too!
  • gerdhuebner
    gerdhuebner Member Posts: 155
    jimmyf wrote:
    ...When the code executes there are still other ship to records for this customer that are still true! I am executing the code at table level on the on validate trigger of the Primary Address field.
    Well, I tried your code right in the beginning of the thread and found it working right. As your last post suggests, the problem was a missing update of the table form if you set one line to principal adress, the checkboxes of the other lines are not updated automatically, so at first glance, when you don't scroll up and down, it seems that there are multiple principal adresses (see picture)


    But there is still another problem, if one copies a principal adress, one will get two of them... - think about it...
    New kits on the blog: https://massivedynamicsblog.wordpress.com
  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    jimmyf wrote:
    Luc Van Dyck wrote:
    Try this code: 
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE(recShipToAddress."Customer No.","Customer No.");
  recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);
  IF recShipToAddress.FINDFIRST THEN BEGIN
    recShipToAddress."Principal Address" := FALSE;
    recShipToAddress.MODIFY;
  END;
END;


    This does not work I am afraid! Thanks for trying :)

    I forgot to set a filter on "Principal Address". This code should work:
    IF "Principal Address" THEN BEGIN
  recShipToAddress.SETRANGE(recShipToAddress."Customer No.","Customer No.");
  recShipToAddress.SETFILTER(recShipToAddress.Code,'<>%1',Code);
  recShipToAddress.SETRANGE("Principal Address",TRUE);
  IF recShipToAddress.FINDFIRST THEN BEGIN
    recShipToAddress."Principal Address" := FALSE;
    recShipToAddress.MODIFY;
  END;
END;
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)

Categories