Text Display Field With Calculation From Two Databases

Panorama X
1

I have two databases:

  • “Expenses” that contains a field named «AMOUNT», number float type
  • “Trips” that contains a field named «TIME», number float type

Both databases hold data for various years and have a «DATE» field formatted as MM/DD/YY.

I need to create a formula that will give me the sum of all «AMOUNT» records for a given year and divide that figure by the sum of all «TIME» records for the same year.

I am planning to put the result on a text display field on a form on “Expenses”

Help will be greatly appreciated.

2

Without fully testing this myself, here is how I would approach this problem. If I simply want to assign the result to a variable for use in a form using a procedure I would first get the year to search either with a gettext or possibly a popup menu button on the form.

Letfileglobal theYear=str(yearvalue(today()))
gettext "Enter year…",theYear

Next I would get the various related values from the Amount and Time fields of the two databases and total them and then divide them for a final result in a variable I call finalFigure.

let amountTotal=arraybuild(cr(),"Expenses","Amount",{yearvalue(Date)=val(theYear)})
amountTotal=arraynumerictotal(amountTotal,cr())
let timeTotal=arraybuild(cr(),"Trips","Time",{yearvalue(Date)=val(theYear)})
timeTotal=arraynumerictotal(timeTotal,cr())
letfileglobal finalFigure=amountTotal/timeTotal

You could also just daisy-chain this all together an use it as a formula for your Text Display Object as long as you have the proper year set in the theYear variable:

arraynumerictotal(arraybuild(cr(),"Expenses","Amount",
{yearvalue(Date)=val(theYear)}),cr())/arraynumerictotal(arraybuild(cr(),
"Trips","Time",{yearvalue(Date)=val(theYear)}),cr())

The Text Display should update any time you change the value of theYear and use showvariables theYear. As I said to start with, I have not fully tested this so you might need some adjusting.

3

This sounds like exactly the kind of task the aggregate( function was designed for.

4

I forgot about the aggregate( function and that could simplify the formula to something like:

aggregate({Amount},"+",{yearvalue(Date)=val(theYear)},"Expenses")/
    aggregate({Time},"+",{yearvalue(Date)=val(theYear)},"Trips")

My poor muddled mind can’t keep track of all the options available in Panorama X - but that’s a good thing I guess.

5

I get “Field or Variable [theYear] does not exist”.

6

The formula assumes you have set a variable (I called mine theYear) to the year you want to use in the formula. If it is a set value like “2019” you would just substitute that for theYear in the formula. If you have the year in some other variable or field you would use that in place of theYear.

7

Pure embarrassment on my side but still the formula

aggregate({Amount},"+",{yearvalue(Date)=val(2020)},“Expenses”)/
aggregate({Time},"+",{yearvalue(Date)=val(2020)},“Trips”)

gives the same error, i.e. Field or Variable [Time] does not exist.
I have confirmed that I indeed have the field Time (integer float) in Trips.

8

If you simply run that part of the formula with a message statement does it give the same error?

message aggregate({Time},"+",{yearvalue(Date)=val(2020)},“Trips”)
9

Yes, it does.

10

Ok, how about simplifying this even more to test. Try running this:

setactivedatabase “Trips”
message aggregate({Time})

This should return the total of the Time field.

11

Same result: Field or Variable [Time] does not exist.

12

What database are you running the procedure in? If it is the Trips database just try message Time to see if you get the same error. If you do I would guess the field name might have an invisible character in it like a leading or trailing space.

13

I have created a Text Display Field on a form in the Expenses database and I have input the formula in the formula tab of such field.

I had the same suspicion about the space or trailing character so I copied/pasted straight from the field name into the formula.

14

I have created a Text Display Field on a form in the Trips database and run message Time.

When the field is set for formula, the error message is “Expression contains an operand when an operator was expected” and when field is set to literal text, obviously, I get “message Time”.

15

You can not run statements like message in a formula. Just put in Time as the formula. To clarify what we are talking about here I am assuming you are using “Text Display Field” to actually indicate a Text Display Object. The field would be placed in the formula pane of the Text Display Object and the Options would be set to ‘Formula’. Some of the earlier code I suggested trying were statements that would be run from a procedure. Statements can contain formulas but formulas can not contain statements.

16

Gary,

Pleased to report that both formulae work when applied correctly:

arraynumerictotal(arraybuild(cr(),"Expenses","Amount",
{yearvalue(Date)=val(2020)}),cr())/arraynumerictotal(arraybuild(cr(),
"Trips","Time",{yearvalue(Date)=val(2020)}),cr())

aggregate({Amount},"+",{yearvalue(Date)=val(2020)},“Expenses”)/
aggregate({Time},"+",{yearvalue(Date)=val(2020)},“Trips”)

I cannot figure out what I was doing differently yesterday from today for the second formula not to work.

Thank you for your patience and help.

17

Glad you have it worked out. I was at a loss as to what else to suggest. :woozy_face:

18

This “simple” calculation has added considerable value to Pan X for me so I appreciate your sticking with me until I could sort it out.
Cheers and stay healthy.

19

Gary,

What code do I need to add to the formulae in order to format the result of the Text Display Object as $###,### ?

Thanks.

20

Surround the formula with a pattern( function.

pattern(aggregate({Amount},"+",{yearvalue(Date)=val(2020)},“Expenses”)/
aggregate({Time},"+",{yearvalue(Date)=val(2020)},“Trips”),"$,")