How to test if an entered value is a member of a value list

[Roger Moffat]

I have a simple online registration system running for an upcoming  
Seminar.

As an incentive to get more people to register, we're thinking of  
offering a "bring a friend" coupon, so that if you've already  
registered, and can convince someone else to register, you would get a  
coupon, and the friend gets a discount (you can't get a discount since  
you're already registered and paid).

To implement this I was thinking of telling the current registrants to  
tell their friends to use a coupon code that is made up your last name  
and your registration number. So for example I'd tell people to use  
Coupon Code "Moffat 1"

But I'm stumped about how to at the time of registration test that  
this code is a valid code, and so if it is, give the new registrant a  
discount

I set up a value list made up of the lastName and the OrderNumber, so  
that this list is continually updated, but I'm stumped on how to have  
the "price calculator" test to see if a value in the CouponCode field  
is a member of that Value List.

Currently the Price calculation is

Case (
(Session1 = "None" and Session2 = "None" and Session3 = "None") ; "0" ;
(Session1 = "" and Session2 = "" and Session3 = ""); "0";
Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and LeftWords  
( CouponCode ; 1 ) = "BringAFriend"; "46";
Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and CouponCode =  
"EASTMAN"; "45";
Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) ; "50";
CouponCode = "EASTMAN"; "54"; "60")

Which is saying

If you didn't choose any sessions (or chose them all to be None), then  
it's $0
If it's before 20 Oct 2009 and you've used CouponCode EASTMAN then  
it's $45
If it's before 20 Oct 2009 it's $50
If the CouponCode is EASTMAN it's $54
Otherwise it's $60 (no coupon and after 20 Oct 2009)

So I need to add a line before or after the first line with CouponCode  
= "EASTMAN" to test if the coupon code is a member of the valueList.

Can someone suggest a way I can accomplish this?

Many Thanks

Roger the Bamboozled FileMaker Person=

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Clayton Randall]

I would make a relation from the entered code to a table of all
registered users related on your computed coupon field.

I would also make a field for expiration date to enable reusage of
this solution.

Then I would set the relationship to be a compound one, CouponCode =
RegisteredUser.Registration Number AND RegistrationDate <=
ExpirationDate

To test if it is a valid code, just count related records, if >0 then
it is valid, if not, sorry bad code

On Fri, Oct 16, 2009 at 7:43 AM, Roger Moffat <[hidden email]> wrote:

> I have a simple online registration system running for an upcoming Seminar.
>
> As an incentive to get more people to register, we're thinking of offering a
> "bring a friend" coupon, so that if you've already registered, and can
> convince someone else to register, you would get a coupon, and the friend
> gets a discount (you can't get a discount since you're already registered
> and paid).
>
> To implement this I was thinking of telling the current registrants to tell
> their friends to use a coupon code that is made up your last name and your
> registration number. So for example I'd tell people to use Coupon Code
> "Moffat 1"
>
> But I'm stumped about how to at the time of registration test that this code
> is a valid code, and so if it is, give the new registrant a discount
>
> I set up a value list made up of the lastName and the OrderNumber, so that
> this list is continually updated, but I'm stumped on how to have the "price
> calculator" test to see if a value in the CouponCode field is a member of
> that Value List.
>
> Currently the Price calculation is
>
> Case (
> (Session1 = "None" and Session2 = "None" and Session3 = "None") ; "0" ;
> (Session1 = "" and Session2 = "" and Session3 = ""); "0";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and LeftWords ( CouponCode ; 1
> ) = "BringAFriend"; "46";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and CouponCode = "EASTMAN";
> "45";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) ; "50";
> CouponCode = "EASTMAN"; "54"; "60")
>
> Which is saying
>
> If you didn't choose any sessions (or chose them all to be None), then it's
> $0
> If it's before 20 Oct 2009 and you've used CouponCode EASTMAN then it's $45
> If it's before 20 Oct 2009 it's $50
> If the CouponCode is EASTMAN it's $54
> Otherwise it's $60 (no coupon and after 20 Oct 2009)
>
> So I need to add a line before or after the first line with CouponCode =
> "EASTMAN" to test if the coupon code is a member of the valueList.
>
> Can someone suggest a way I can accomplish this?
>
> Many Thanks
>
> Roger the Bamboozled FileMaker Person=
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
> Manage your subscription: http://www.ListSearch.com/FileMaker/
>
>
>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Roger Moffat]

On Oct 16, 2009, at 7:43 AM, Roger Moffat wrote:

> Currently the Price calculation is
>
> Case (
> (Session1 = "None" and Session2 = "None" and Session3 = "None") ;  
> "0" ;
> (Session1 = "" and Session2 = "" and Session3 = ""); "0";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and LeftWords  
> ( CouponCode ; 1 ) = "BringAFriend"; "46";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and CouponCode =  
> "EASTMAN"; "45";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) ; "50";
> CouponCode = "EASTMAN"; "54"; "60")

Sorry - the Send Button is as usual a great proof reader...

The calculation currently is

Case (
(Session1 = "None" and Session2 = "None" and Session3 = "None") ; "0" ;
(Session1 = "" and Session2 = "" and Session3 = ""); "0";
Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and CouponCode =  
"EASTMAN"; "45";
Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) ; "50";
CouponCode = "EASTMAN"; "54"; "60")

The extra line was something else I thought of to use a fixed coupon  
code followed by a current registrant's name - this lets me track  
whose name got used, while looking for just a single CouponCode.

And this is currently on FileMaker Server 9

Roger=

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/

[Steve Cassidy]

Roger

I think I'd be doing this in a very different way...

1) Where do these prices come from? I say they should be in a table of  
products and that they should be looked up into each sales record at  
the time of the sale/registration. One extremely good reason for not  
doing it by calculation (as you describe) is that, if the price  
changes, all your old records (invoices? registrations?) suddenly get  
the new price. Not good for your bookkeeping...

2) You should have a product record for each session -- which  
presumably in your case would mean for each date range. Each product  
would have fields for the standard price and the discounted price.

3) I'd look up both prices into the sales record from the product  
record. Then in the sales record there'd simply be a calculation that  
would take the appropriate looked-up price depending on the validity  
of the coupon code.

4) You'd test the coupon code simply through a self-relationship to  
existing records.

That's just an outline. Maybe it will give you some food for thought?  
You will find it worthwhile in the end, as you are going to get in a  
real twist with calculations like the one below...

Steve


On Oct 16, 2009, at 1:16 PM, Roger Moffat wrote:

> Sorry - the Send Button is as usual a great proof reader...
>
> The calculation currently is
>
> Case (
> (Session1 = "None" and Session2 = "None" and Session3 = "None") ;  
> "0" ;
> (Session1 = "" and Session2 = "" and Session3 = ""); "0";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) and CouponCode =  
> "EASTMAN"; "45";
> Get ( CurrentDate ) ≤ Date ( 10 ; 20 ; 2009 ) ; "50";
> CouponCode = "EASTMAN"; "54"; "60")
>
> The extra line was something else I thought of to use a fixed coupon  
> code followed by a current registrant's name - this lets me track  
> whose name got used, while looking for just a single CouponCode.
>
> And this is currently on FileMaker Server 9
>
> Roger=

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/