Exlusive boolean

jimmyf

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;

gerdhuebner

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.

Savatage

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);

Luc_VanDyck

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;

Savatage

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.

jimmyf

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

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

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

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;

jimmyf

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

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;

jversusj

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)?

jimmyf

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

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

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

gerdhuebner

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

Luc_VanDyck

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;