Invalid Expression in Component

[Howard G. Cornett]

I keep having Alpha Five tell me contradictory things. When I am building a calculated field in a grid component, I generally use the expression builder. Sometimes when I am done building the expression, it shows the value at the bottom of the expression builder (indicating a valid expression), but when I click OK and go back to the Define Calculated Fields screen, there it shows the expression to be invalid. And if I click on OK on the Defined Calculated Fields screen, I get an error. Why is that?

Here is an example of an expression that causes this behavior:
ST1wkMA = tableavg("ratings","Symbol='"+Symbol+"' .and. "+between_date("Date",Date-6,Date),"Rating")

Symbol, Date, and Rating are all fields in the table Ratings. Attached is the error message I get with this expression when I click on OK in the Defined Calculated Fields screen.

Howard

[JerryBrightbill]

RE: Invalid Expression in Component

The context in which an expression runs is very important. It may work in one context, such as the expression builder, but fail in another if all value references can't be resolved. From your use of "+Symbol+", it would appear that symbol is not only a field name, but a variable. While that may not cause a problem, it isn't good practice. Variables are very touchy about context. Where does the variable exist? Is it local, global, session, etc. My own experimentation with calculated fields in the component builder has lead me to avoid all variables in the component. If I need to change the expression to use a session or local variable, I will change the related xbasic lines when the component is added to a page and I have a session or local variable value available there.

Jerry

[Howard G. Cornett]

RE: Invalid Expression in Component

I am not using any variables in the component.

Howard

[Howard G. Cornett]

RE: Invalid Expression in Component

Symbol is a field in the table as is Date. As I mentioned above, there are no variables.

Howard

[Cheryl Lemire]

RE: Invalid Expression in Component

If you are not using symbol as a variable, why:

"Symbol='"+Symbol+"'

Wouldn't you just need "Symbol"

[Selwyn Rabins]

RE: Invalid Expression in Component

it is so hard to answer these questions when they are 'theoretical' and so easy to answer the question when you attach a sample that demoes the problem and you describe how to duplicate it using the attached files.

instead of getting a thread full of multiple messages that are nothing but pure speculation, you get one message with an answer that is spot on.

in other words, please post your files and describe how to reproduce the problem.

[Howard G. Cornett]

RE: Invalid Expression in Component

No, because I want the field called Symbol (the first occurrence) to equal the value of the current record (the second occurrence).

Howard

[Howard G. Cornett]

RE: Invalid Expression in Component

Selwyn,

As you requested, my sample data is attached. Open the database. Open the Web Projects Control Panel. Edit the "reporting" Web Component. Click on the "Calculated Fields:" button in the Query (DBF) section. Click on the bottom expression. You will notice that at the bottom it says "invalid expression" in red. Now go to the expression builders. It shows a result for the same filter (not invalid).

Now add another calculated field, like day=date(), and click OK in the Define Calculated Fields window. You will get the error message that I referred to in my first email.

Let me know if you need any more info and thanks!

Howard

[Cheryl Lemire]

RE: Invalid Expression in Component

Hi Howard,

OK, so you have specific reasons for doing things the way you are. You really need to attach your files and give us detailed steps to duplicate your problem. This is the only way we can really resolve your issues without wasting so much time.

Cheryl

[Howard G. Cornett]

RE: Invalid Expression in Component

Cheryl,

Scroll down a little. :-)

Howard

[Selwyn Rabins]

RE: Invalid Expression in Component






New Page 2




Howard.
Thanks for the files. Unfortunately, the problem is not happening for me. I
can add multiple new calc field, and then every time I give focus to the ST1wkMA
calc field, the status line shows a valid result.
Are you able to duplicate this problem every time you edit the calc fields?
When the problem does occur, you might try to see what part of the expression
is causing a problem by simplifying it.
Another thing you might try is re-writing the function as follows:
tableavg("ratings","Symbol=\""+Symbol+"\" .and. date "={" + (date-6) + "}
.and. date "= {" + date + "}" )
This is the same as your previous expression, but it does not use the
high-level function between_date(), (which I actually think is more of a
confusing than helpful function).

[Howard G. Cornett]

RE: Invalid Expression in Component

Yes, it happens every time with this calculated field. I even tried your version, and once again it works in the expression filter but shows invalid in the Define Calculated Fields window.

The part that seems to be causing the problem is the date part.

Howard

[Howard G. Cornett]

RE: Invalid Expression in Component

I even tried re-creating the component from scratch and I get the same problem.

Howard

[Selwyn Rabins]

RE: Invalid Expression in Component

so, if you replace the date part, (which is the filter) with ".t." (which is also a filter, but is always true), does it always work?

[Howard G. Cornett]

RE: Invalid Expression in Component

Selwyn,

I changed the expression to ST1wkMA = tableavg("ratings","Symbol=\""+Symbol+"\" .and. .t.","Rating") and it still come up as "Invalid Expression" in the Define Calculated Fields window. See attachment.

Howard