Sage 100

Is there a way to use variables in an ALE lookup, like %today%? For example, I always want someone's lookup to default to today, without them having to change it every day.

Oh, @DavidSpeckII - are you out there?

yep. 1. Set up a new calculated field, call it ""Today"" or something. 2. In the calculation, put the following ""STR(OrderDate) = STR(DTE(0:""%M/%D/%Y""))"". 3. Set up a pre-defined filter on the new field where it is ""equal to 1"" That's it.

Ah, very cool! Thanks @DavidSpeckII (and @BethBowers )

you can also use this trick to limit a lookup to only return records the user created by using this ""STR(SO_SalesOrderHeader.UserCreatedKey) = %SYS_SS'UserKey$

Positively brilliant. Thanks, @DavidSpeckII !!!!

Wow!! Who knew?? (Besides Beth.)

I only knew David had done it - I certainly wouldn't know how to do it!

Oh Wow! thank you @DavidSpeckII

This post, http://sagecity.na.sage.com/support_communities/sage100_erp/f/97/t/38968, is where I found out you could use the %SYS_SS global variable. Then I started experimenting and found that you can also use ProvideX functions in the calculated field and adapted it to work for date fields. You can do a lot as long as your calculated field returns a boolean value (1 or 0). Dealing with string fields is a little tricky though since you can use a string property of the %SYS_SS variable by adding the dollar sign but that does not work (at least in v2015 it doesn't) for fields, you have to wrap the field in the STR() function if you are going to compare it against a string property. Also, the reason for using the DTE() function instead of the SystemDate$ property of %SYS_SS is because SystemDate$ returns the value in YYYYMMDD format and the lookup sees a date value in M/D/YYYY format.