PDA

View Full Version : Help with the expression genie


ABC123

Penny
01-19-2009, 10:59 PM
Hello, I am new - have posted newbie questions within the last couple of weeks....
I am having difficulty with the expressions. I look at other examples within the A5V9 but do not "have the knack" to writing the expressions. I could "wizard" my way around the less complex functions in Excel - but finding myself truly stuggleing with A5 expression builder.

What I am attempting to create is a value (number of days based on several items) that could be returned based on the Status of a load (A-active,C-shipped, or blank). If the status of the load is A, then return the value System date <-> date received (number of days)....if C, then return the value from Lotship(table)Dateship (field) - Daterec, lastly if status is blank return 0 or " ".
What was used with the old version (A4V7) was the following:

IF(Status="A", System->Date-DateREC, if (Status="C",Lotship->Dateship-DateREC, If (Status=" ",System->Date->,System->Date,System->Date-System->Date)))

This is used on a form (multiple use) at new input, at browsing, at shipping.
Thank you for any help you can give me.

Stan Mathews
01-19-2009, 11:07 PM
Case() (http://support.alphasoftware.com/alphafivehelpv8/Functions/CASE().htm) is much easier to construct, IMHO.


Case(Status="A", System->Date-DateREC, Status="C",Lotship->Dateship-DateREC, Status=" ",System->Date)

The first true statement working left to right causes the paired (color indicated here) result to be returned.

I would suggest you use date() rather than system->date.


Case(Status="A", date()-DateREC, Status="C",Lotship->Dateship-DateREC, Status=" ",date())

Penny
01-25-2009, 03:13 PM
I placed this into the calculated field (expression area) have got the invalid or incomplete expression.

Stan Mathews
01-25-2009, 03:41 PM
Hard to diagnose without a sample database and/or the context.

You can start by building something that does work and expanding. Try

Case(Status="A", date()-DateREC)

if this works, try adding the other logical test,result pairs one at a time. If it doesn't you may need to qualify the DateREC with its table name

Case(Status="A", date()-sometablename->DateREC)

Are the DateRec and Dateship in the same table? Is the form built on a set using tables containing both fields?

Penny
01-25-2009, 03:53 PM
Well, I am failing my task. I feel perhaps that I am not at all correct in my execution. Would it be better if I zipped the database and emailed?

Penny
01-25-2009, 03:56 PM
DateRec is in Lothead, Dateship is in Lotship.
Form based on set with both tables, yes

Stan Mathews
01-25-2009, 04:08 PM
You can attach a zip of your database here. Meanwhile try

Case(Status="A", date()-Lothead->DateREC)

I neglected to ask where the status field is. You may need to qualify that as well.

Case(Lothead->Status="A", date()-Lothead->DateREC)
or
Case(Lotship->Status="A", date()-Lothead->DateREC)

depending.

Normally you do not need tablename-> for the primary table of the set (it is assumed) but you do need it for all other fields from other tables.

phuongtim39
03-25-2010, 06:53 AM
I accept with information:that could be returned based on the Status of a load (A-active,C-shipped, or blank). If the status of the load is A, then return the value System date <-> date received (number of days)....if C, then return the value from Lotship(table)Dateship (field) - Daterec, lastly if status is blank return 0 or " ".