Calculated Field - Expected Value

[forskare]

I have a mapped table. In the grids' calculated Fields, The expression builder says the expressions are good to go, showing the amounts as I step through the records. However, the calculated values window does not like the expressions, stating in bright red, Expected Value. Does a mapped table require further code? If so, what might that code be? If not, any ideas why the expression builder agrees with the expressions but the calculated values window says, 'not so fast'?

[CharlesParker]

Re: Calculated Field - Expected Value

Ken, not sure whats going on there or how to even begin to guess. We don't know so much about whats going on. I would suggest a video of your issue, unless you can really clarify whats going on. For instance what exactly is the "calculated values window?" is this a message box popup? It sounds to me like something is missing in the calculation, perhaps if you left out a value or you have or have not checked NULL if blank (in a UX) then the calculation might be trying to calculate a char field...so many scenarios.

[forskare]

Re: Calculated Field - Expected Value

Hi Charles,

This a grid built on a mapped table. Even though it's a mapped table, the fields are all from 1 of the 3 tables which make up the mapped table. Exp-1 shows the expression builder. Exp-2 shows the Calculated Values window, which pops up when the Grids' calculated Fields button is pressed (Grid Query view for selecting the table). Exp-3 shows both together. AS you can see, the expression builder it works fine but in the Calculated Values window, it wants another value. Independently, the same expression in the regular table, works OK so it must be something to do with the Mapped Table. Hopefully, this will be of some help.

[GGandhi]

Re: Calculated Field - Expected Value

if you have defined a calculated field in the mapped table why do you need to redefine the same field in the grid builder, is that field not available?

[forskare]

Re: Calculated Field - Expected Value

Hi Gandhi,

The expressions must be defined in the grid. They will not work when defined in the table itself. Using a field, such as Total Invoice, and defining the expression in the fields Calculated Field Expression will work but the Display Format does not work. Special formatting must be used as noted in the Important Information link at the top of the Display Format window. Unfortunately, the suggested format does not work either because the OK button does not activate. I tried 2 suggested formats:

Alltrim(str( (Sale*Comborate),250,2,�,�))

alltrim(st(<value>,250,2,�,�))

Neither will trigger the OK button. That leaves the window for defining the calculated fields in the grid itself.

EDIT: The expressions work when using the grid which is based on the non mapped table. Obviously, it has to do with the mapped table. More code needed? A bug? Documentation? The only reference to a calculated field I found in the mapped table documentation is the reference to Combining the firstname and and lastname fields into one. It referrs to the field with the original table, not the mapped table.

I tried doing that. salestax= if(tlc_3apps->Exempt="Y",0,tlc_3apps->Sale*tlc_3apps->Comborate). Now the error message is that the Exempt field is not found. If I remove the table name, then it's back to the 'expected value' error. Time for a break.

[CharlesParker]

Re: Calculated Field - Expected Value

Try just putting this for the calculated field value: if(Exempt="N",Sale*Comborate,"0")

[CharlesParker]

Re: Calculated Field - Expected Value

I would create some placeholder fields if the mapped tables are causing an issue - then just perform the calculation and save it within the table your after.

[GGandhi]

Re: Calculated Field - Expected Value

can you please post sample fields from your table ( not necessary to include all the fields only the one that matters to do the map and calculations)

[forskare]

Re: Calculated Field - Expected Value

can you please post sample fields from your table ( not necessary to include all the fields only the one that matters to do the map and calculations)

salestax= if(tlc_3apps->Exempt="Y",0,tlc_3apps->Sale*tlc_3apps->Comborate)

Salestax - The field in the grid( Not the mapped table)
Exempt - Field in the mapped table
Sale - Field in the mapped table
Comborate - Field in the mapped table

The Salestax is determined by: if the Exempt field value = "Y", then the salestax value is 0, else the salestax value is determined by multiplying the value in the Sale field by the tax rate in the comborate field.

[forskare]

Re: Calculated Field - Expected Value

There's not much info on Mapped Fields and I have not been able to identify the issue is with defining calculated fields in the Query/Calculated fields box. Perhaps Alpha cannot add these fields to a mapped table using this method. It would be most helpful to all if Alpha would shed some light on this.

I did, however, find 2 ways to define a calculated field in a mapped table.

1) If the field is already in the physical table, go to the Fields' Field Properties and define the expression in the Display Expression box.

2) If the field is not in the physical table, it can be added by clicking the 'Add Computed Column' link located just below the field selection list and defining the field and expression there.

The rest of the formatting can be defined in the form, browse, report, grid or ux.

Edit:

Since a Mapped table can be made 'Updateable', It can also be used to data entry. For instance, if you have a need to update or perform data entry with 2 or more fields but only need a few fields, a mapped table might be a solution.

[GGandhi]

Re: Calculated Field - Expected Value

Perhaps Alpha cannot add these fields to a mapped table using this method

it is quite possible that the mapped table is in-memory table not a physical table alpha cannot access the fields to calculate a new field until those tables are combined, in memory of course. i was not interested in your mapped tables. i wanted to see your tables (without any mapping) and your desired outcome. many times another pair of eyes will spot the solution that is evading lot easier than the original. definitely i can vouch for that from my own experience. it is quite possible you have solved your situation, if so very good.

edit:
from alphapedia:

A mapped table is a virtual table that displays data from one or more physical tables

[forskare]

Re: Calculated Field - Expected Value

Hi Gandhi,

All the fields in the expression are from the physical table except the outcome field, which of course, has to be from the virtual table. In my experimenting, it makes no differene if the mapped table is in a state of creation or has been saved. The behavior is the same.

Mapped tables are a great feature and can play a vital roll in an application be it in a desktop or web application. Come to think of it, I remember some talk several years ago about a new method on the horizon to link tables without using a set. If Alpha would tweak this a bit, how great would mapped tables be for displaying data, reports, etc. The ability to use a mapped table in a web app is really, very cool.