New basic understanding about field formulas vs. field code

Panorama X
1

New to PanaromaX. Plan to use it analyze the deluge of data generated by my home energy management system. I want to import CVS files and append to a database(s) on a regular basis.

I have been watching, reading and learning to understand how I can accomplish what I want. As always there are many ways - just trying to figure out the most flexible approaches.

My problem is understanding how to use the field formula and field code.

3 Questions:

  1. I have an imported field that I want to evaluate to create the value in another field, i.e. if fielda >2 fieldb=“true” else fieldb=“false” etc. Since this requires code and not a formula I can’t do upon import (right?). So in field definition do I put the code in fielda or fieldb? Does it matter? If I put it in fielda then it execute when I edit fielda. If I put it in fieldb then I need a runfieldcode"fieldb" in the fielda field code. Is one way better than the other?

  2. Seems like either way, fieldb is not populated upon import. How do I get the fieldb to be populated? The morph/recalcuate field works on field formulas but not on field code (right?).

  3. I will have several new fields (not imported) that will have field formulas. Is there a way to morph/recalculate multiple fields at once. I think I understand procedures can do this but just want to make sure there isn’t another option. Haven’t tackled procedures yet.

Also I found this
"As an alternate to setting up code for specific fields, you can also set up a special procedure named .ModifyRecord that will be run whenever any field is modified. See Tracking Database Changes for more information."
But the link “Tracking Database Changes” take you to a embarrassing page referencing plans from 1996 :slight_smile: Haven’t dug into .ModifyRecord yet. Thought maybe there was something simply I wasn’t yet understanding.

Thanks in advance for any assistance.

Jim

2

For what you are describing, you probably want to put this into the import itself. The formula for “rearranging” the fields can also generate new text. See the documentation for the Text Import Wizard

and in a procedure, the importtext statement.

3

As for many things in Panorama X, there are several ways to accomplish a task. My first advice is: Keep things simple.

While you can do calculations during the import (Dave mentioned already the importtext statement; you can combine this statement with a importusing formula), I would recommend to simply import your csv file with its fields and to add more fields to the database for your calculations later.

Sometimes it is better to divide a task in smaller steps (and to build the more complex, fully automatic structures when you know the application better).

4

Actually, importusing is a deprecated statement. In Panorama X, its functionality is now the “rearrange” option in the importext statement itself.

5

Thanks for the feedback.

I looked over the suggestions. I think I understand the capabilities of the import text statement and the import text wizard.

I conclude that I can’t execute field code upon import (only field formulas).
To populate the fields dependent on field code in a field that was imported, I found that I could create a procedure to loop thru all the records and ‘edit cell’ on each field containing field code. This would execute the field code and update additional field. This works but takes many minutes for my 20000 records. I will find a way to do this only on the records that are imported and not the entire database.

Believe me I love to keep things simple. I was hoping a could find a simpler solution.

Btw - could not find any help info on the importusing formula. I found a few links to that command but it takes you to ‘under construction’ type message.

Thanks again for your suggestions.
Jim

6

Here is something to try in speeding up all the field changes. This will first restrain screen redraws and then jump through each record and force all the fields in turn to execute their automatic code. When finished it will redraw the data sheet with all the changes.

noshow
firstrecord
loop
    looparray info("fields"),cr(),element,index
        runfieldcode element
    endloop
    downrecord
until info("stopped")
showpage
endnoshow
7

As Dave already noted, the importusing statement was a reminiscence of Panorama 6. That feature is now part of the importtext statement (see “REARRANGE” option).

During the import, you can already do many things (incl. changing the order of imported fields or calculations), but you don’t have to do so. You can do anything after the import, and you do not need slow loops.

For example:

importtext fileload("MyFolderPath"+info("databasename")+".txt"),"ExistingData","Replace"
call "Auswertung"

I import a text file with soccer results replacing previous data.
Then my procedure “Auswertung” e.g. calculates (in a separate field) a superdate from the imported fields “Date” and “Time”, and in other fields it calculates points, goal difference and goals, and at last it prepares a typical soccer table.

You can do any calculations with the Field > Morph … menu commands — or procedurally. Write your procedure from scratch or record it while doing the desired actions manually. Call your procedure with the Action menu (or with a keyboard shortcut).

8

Thanks again. I am getting there slowly. Lots to try.

Jim