Where Does a Formula Go?

Hello,

I’m in my trial week with Panorama, moving data from FileMaker and getting things set up. I’ve used the Relational Workshop successfully to get information from one database (“A”) and interpret it for use by another (“B”)—really easy. Then the Help information states “Once everything is complete, you’ll need to copy the finished code to the procedure or object where it will be used.”

Here is where I’m stuck: where and how do I copy the finished code to the procedure or object for use? I’ve tried clicking on the object in database B and pulling the code into the Formula pane, but that doesn’t work. I’ve tried pulling the code into the Procedure pane, still no go. I then went into the Formula Workshop where the code will go, but now I think I have to add a Statement to get it into the database, but which one?

This is all probably ridiculously simple, but as the old Russian saying has it, it’s not the ocean that drowns you, it’s the puddles. Can anyone help me with the obvious step am I missing? Much thanks.

There are several ways to get the formula from the Relational Workshop to your procedure or the Formula pane of a form object. The first is to simply highlight the formula text, copy it and then paste it into the proper procedure or object Formula pane. The second way is to highlight the text and then drag it to the Formula pane of an already selected form object or a procedure window.

The third way is to simply click the Copy button in the Code bar above the formula and then paste it as before.

If this formula is going into a procedure it needs to be part of an assignment or statement (it can not stand alone).

The formula: lookup(“Test Invoices”,«ID»,«A»,«Name»)

could be part of an assignment:

customerName=lookup("Test Invoices",«ID»,«A»,«Name»)

or part or a statement:

Select Name=lookup("Test Invoices",«ID»,«A»,«Name»)

A Formula pane for a form object expects a simple formula so the transferred formula is all that is entered.

Ah, I see—I was using a Text Editor object, not a Text Display object. With the latter, the different ways of getting the formula into the Formula pane now all work. Thanks for the direction!

Another way that formulas can be used is in the Formula panel of the Data Sheet inspector panel. You can copy a formula from the relational workshop into this panel.

Or, sometimes it’s useful to use a relational formula in an assignment as Gary described, and put that code in the Code panel of the Data Sheet inspector panel.

An example of using a lookup( function this way is shown on this tutorial page, under the heading Extra Credit: Automatic Lookup of Category and Debit.

Note that when formulas are set up this way, they will not just work in the data sheet, but also in forms. The automatic calculations and forms will be performed whenever a field is edited, whether in the data sheet or in a form.

Much thanks for this info. Right now, I’m reverse-engineering a FileMaker database and layout to feel my way around Panorama, but when I begin creating a new version of the database from scratch this will be very useful.

I’m not a FileMaker expert, but it’s my understanding that in FileMaker you have to set up a special “calculation field” for each formula you want to use. Panorama doesn’t have calculation fields, instead you just type in a formula wherever you want to use one.

This is one of the (several) things that I’m finding so much easier to work with in Panorama. I am having to get up to speed with formulas and procedures (and which is which) though.

TLDR – if you need a value, use a formula. If you want to perform actions, use a procedure.

Formulas calculate a value. Unlike mathematical formulas, the calculated value can be a number, text, or a binary value, but there is always a value produced as the result. Formulas are built up from constants, operators and functions.

Procedures perform actions – open a window, sort the data, etc. There is no value calculated. Each action is performed by a statement.

Many procedure actions use formulas to calculate needed value. For example, if you want to open a form, you need to use a formula to calculate the name of the form. (Of course usually this formula is just a simple constant, but it doesn’t have to be.) I think this sometimes causes confusion, because procedures almost always contain formulas mixed in.

There is a method where a formula can have actions mixed in, but that is a rare advanced technique so you shouldn’t worry about it.

This helps enormously. You’re right about the source of confusion—so many of the examples I consulted did have formulas inside of procedures. Thanks again.