Global "True" Field vs. Non-Global?

[RWX]

The solution I'm currently developing requires that I set up some calculated true/false relationships. An example is the coupon tracking. The user creates a coupon, enters a dollar value, an expiration date, and links select company clients to the coupon record via a join table. In the join table there is a calculated field that returns either a 1 or 0 based upon today's date and the expiration date. When the user creates an invoice record, he/she is given a list of valid coupons for the client on the invoice. The user may then choose to apply a coupon to the invoice record. Pretty basic stuff.

Right now I have the coupons appear based on the following relationship:

Invoice -> Coupon Join -> Coupon

Coupon -> Coupon Join is a basic join using the Coupon's ID field value. Invoice -> Coupon Join takes the client ID value stored in the Invoice record and the client ID value in the Coupon Join record(s). It also uses a global number field in the Invoices table that receives a value of 1 upon user login. This "true" field is matched to the calculated field in Coupon Join that selects either a 1 or 0 based upon today's date and the coupon exipiration date.

Or, actually, it's not. :-) That's how it's set up, but the relationship does not work. I assume that is because global to calculated relationships are a no-no? I've come across this same issue in several places throughout my project and always dealt with them by going from a calculated field to a standard indexed number field. In this case, the calculated coupon valid flag in the Coupon Join table would become an indexed number field. I could then run a script at login that finds expired coupons and changes the expiration flag value.

My question is this...

Is the global "true" field the way to go? Or would I be better off simply having a standard indexed number field on each Invoice record that receives a value of 1 upon creation? Pros and cons? Obviously, the standard number field would increase file size a bit over time. But I could use calculated relationships, something I cannot do using the global field. Also, what about network usage issues? Could the global relationship cause any issues when the file is hosted on a server?

Any and all thoughts greatly appreciated!

-Rob

[Ender]

The global parent key is fine, and I would recommend keeping it (rather than using a calc=1.) The problem you are having is with your match key. Since calcs with Get(CurrentDate) cannot be indexed, they can't be used as match fields in relationships.

It sounds like your match field could be made with an auto-entered calc instead of a regular calc. Since auto-entered calcs are part of regular fields, they CAN be indexed, and therefore can be used as match fields in relationships.

[Søren Dyhr]

That's how it's set up, but the relationship does not work. I assume that is because global to calculated relationships are a no-no? I've come across this same issue in several places throughout my project and always dealt with them by going from a calculated field to a standard indexed number field

You seem to be carried away with global fields, eventhough carthesian products made them less in demand ...is this problem the similar to

this???

--sd

[Fenton]

If you want Get (CurrentDate) to actually BE current, then it has to be Unstored. So why not put it on the LEFT side; well, we're not really doing "left" and "right" in 7; but I mean relate an Unstored Get(CurrentDate) field -to-> the Expiration Date. You can use an operator to only get coupons that are still good.

Get ( CurrentDate ) field <= Exp. Date

If you have more criteria you'll have to add them to the relationship.

AND

CompanyID = CompanyID

whatever

It just doesn't work to put Get ( CurrentDate ) in an indexed calculation. Not unless you run a Replace "Today" script on all the relevant records, every day.

[RWX]

You seem to be carried away with global fields, eventhough carthesian products made them less in demand ...

You may be correct. :-) However, this is the only solution I have come up with thus far. The problem is, my client wants this system to look and feel like an application, not a FileMaker database. More importantly, they want to avoid as many potential data entry errors, etc. as possible. So, globals come in handy as I can temporarily store data or create a relationship on the fly for error checking purposes. I've needed to do this in many places. The solution is quite large and complex. It handles everything from client records to a user schedule to invoicing and accounting to class/product management to class enrollment, etc.

My goal with coupons is to only display valid coupons. When the user creates an invoice for a student, only that student's valid and unused coupons (if any!) should appear in the list of available coupons. That's my problem here. The way I tried to set it up is a relationship between the Invoice table's global "true" field (ie: value of 1) and the Coupon Join table's calculated ValidFlag field. This relationship also requires that the StudentID on the invoice record match the StudentID on the Coupon Join record. The Coupon Join's ValidFlag field is based upon the date stored in the related Coupon record's ExpirationDate field. The ValidFlag calculation looks at the ExpirationDate and sets a value of 1 (thus enabling the coupon to appear on the invoice) if today's date is less than or equal to the expiration date.

I guess my problem lies in the fact that the Invoice table's GlobalTrue field cannot be indexed and neither can the Coupon Join table's ValidFlag field. If one can be indexed (ie: changing the GlobalTrue from a global to an indexed number field where every record receives a value of 1), the system works.

-Rob

[RWX]

If you want Get (CurrentDate) to actually BE current, then it has to be Unstored. So why not put it on the LEFT side; well, we're not really doing "left" and "right" in 7; but I mean relate an Unstored Get(CurrentDate) field -to-> the Expiration Date. You can use an operator to only get coupons that are still good.

Get ( CurrentDate ) field <= Exp. Date

If you have more criteria you'll have to add them to the relationship.

AND

CompanyID = CompanyID

whatever

It just doesn't work to put Get ( CurrentDate ) in an indexed calculation. Not unless you run a Replace "Today" script on all the relevant records, every day.

Right. The problem with your suggestion is that I don't care about what's in the Coupons table. I only care about the Coupon Join table. Coupons are joined to Students via the Coupon Join table. A student's coupon (join record) is valid so long as the related Coupon record has no expiration date stored OR today's date is less than equal to the expiration date OR the coupon has not been used. Once the coupon (join record) is no longer valid, I need it to drop from the list of available coupon's when creating an invoice for the student. That's why I need the calculation to work. It seems that if I want to keep the global True field in the Invoices record, I need to also store the ExpirationDate in the join table (via a lookup when the join record is created) so that the calculated ValidFlag field can be indexed since it would no longer refer to a related field, but rather one local to the join table.

-Rob

[Søren Dyhr]

Once the coupon (join record) is no longer valid, I need it to drop from the list of available coupon's when creating an invoice for the student. That's why I need the calculation to work.

I would say you never get it to work, no matter how ugent you need it to, unless you seek shelter in selflookups in order to index the key field in the join, but it's a clumsy datastructure to build, and fills up a lot due to the redundant storage of data.

Why not instead use the relational operators "<" or ">" and then make the parentkey a unstored calc' Get(CurrentDate) ...when they're around.

Another option is to collect the ID's of the join-records return delimited in a field in the invoice - this field is then related with the ? (unequal) operator ...this will also make irrelevant records vanish from the portal.

--sd

[RWX]

I would say you never get it to work, no matter how ugent you need it to, unless you seek shelter in selflookups in order to index the key field in the join, but it's a clumsy datastructure to build, and fills up a lot due to the redundant storage of data.

Why not instead use the relational operators "<" or ">" and then make the parentkey a unstored calc' Get(CurrentDate) ...when they're around.

Another option is to collect the ID's of the join-records return delimited in a field in the invoice - this field is then related with the ? (unequal) operator ...this will also make irrelevant records vanish from the portal.

The reason this won't work is that the coupon may become invalid for several reasons, hence the need for a calculated field to determine whether or not the coupon is valid. A calculated field, ValidFlagCalc in the Coupon Join table (Coupons are set up in the Coupons table and the Coupon Join table joins the Students to various coupons; many-to-many), is set up as follows:

If (( Get ( CurrentDate ) < ExpirationDate or IsEmpty(ExpirationDate) = 1 ) and CouponUsedFlag = 0; 1; 0)

So, the calculation makes sure that today's date is less than the expiration date or there is no expiration date and the CouponUsedFlag field is set to zero. When the coupon is used, a script sets the flag to 1 for that coupon, records a date used, amount applied, etc.

While accessing an invoice record, the user sees a portal with all valid coupons for the student on the invoice. The way I imagined the relationship working is:

Invoice Table

StudentID

TrueFlag (global; value = 1)

Coupon Join Table

StudentID

ValidFlagCalc (if valid = 1; else 0)

The StudentID fields are related, as are TrueFlag and ValidFlagCalc, both with = operators. The problem is, since TrueFlag is a global and ValidFlagCalc is an unstored calculation, the relationship doesn't work. If I make TrueFlag a regular number field (each invoice record receives a value of 1 in this field upon creation) and index it, the relationship works.

I guess I could also do away with the calculated flag field altogether by using a script to set whether or not each Coupon Join record is valid. All new records would automatically receive a value of 1 in the ValidFlag field upon creation. A script could run at login that finds all expired coupons and changes the

ValidFlag field value to 0 for those records. When the user applies a coupon, that script would also change the ValidFlag value from 1 to 0, thus marking the coupon as no longer valid. A bit more manual, especially the login script, but it should work too.

-Rob