I’m trying to create a formula for a field in my auto database. The formula field is called miles. For the formula I need not only the mileage from this record but also the mileage from the previous record – a simple task in a spreadsheet. I’ve spent a couple of hours trying to figure out how to get that value. Direction would be very much appreciated.
1 Like
If you are talking about the previous record, downrecord is your friend. Just remember to use uprecord afterwards.
If you are talking about the previous record with a particular entry in it, use lookupall, which returns an array, and take the second element from the array. You may not want that, but I thought the information would be good in case someone else looks in.
If your database includes a field named Index in which each record is sequentially numbered: 1, 2, 3 etc. you can lookup the value of theField in the previous record:
lookup("yourDatabaseName",«Index»,«Index»-1,«theField»,"")
Just substitute whatever you’re using for yourDatabaseName, Index and theField.
Thanks for the prompt response. Not sure how to use the downrecord function and could not find it the list of functions.
It appears that I need to learn more about writing code for formulas in PanoramaX. I come from a FileMaker background where their Script Editor did most of the coding. Are there PanoramaX videos that might help?
Thanks
Thanks for the response but you’re way over my head – see response to BruseDeB.
A good way to learn programming procedures is to record the steps you would use if you were doing this manually. You would open a new procedure, and click the record icon. Then go to the data sheet, start at the field you want to copy, click on the Down icon, copy what is there, then click on the Up icon, and paste it there. When you stop recording, it will write a procedure with those steps.
This will work, but you should edit it so you are using a local variable instead of copying and pasting.
A Procedure seems akin to FileMaker Script Maker. I’m trying to create a formula in a field.
I’m using the Formula Workshop. In a file that I’ve called Miles in the Formula box I’ve input the mileage from the current record and then want to subtract the same field from the record above.
I’ve tried this: Mileage-uprecordMileage. But it is incorrect.
UpRecord is command that moves up one record within the database just as if you clicked on it or used the up arrow.
To subtract Mileage recorded in one record from the mileage in another record, you need to acquire it by, for instance, going up one record and putting the value of the milage into a variable. Then go back to the original record and perform the math.
The result would be something like:
UpRecord
Let lvMiles = Mileage
DownRecord
Mileage = Mileage-lvMiles
So this little procedure is starting on the record where you want the result, moving up to the proceeding record and loading the Mileage into a local variable named lvMiles, then moving back down to the original record and replacing the mileage with the adjusted result.
I’ll guess that you actually want to keep the Mileage, so you would probably put the difference into another field, for instance, Miles = Mileage-lvMiles.
There are other ways to do this too, but this is a simple enough method for you to start with.
Thanks. I now understand how the procedure works. When I run the procedure from the Miles field , it enters the correct data. I used this:
UpRecord
Let lvMiles = Mileage
DownRecord
Miles = Mileage-lvMiles
There must be a way not get the procedure to run without bringing up its window and hitting the run button, like entering data into the mileage field.
Procedures can be triggered in many ways. Among them is the act of entering data in the Mileage field. In the Datasheet, it can be written right into the Field Properties
or it can be saved as a procedure and called from there but still triggered by data entry into the field
BTW, the fact that my example is named .mileageCalc means that it does not get listed in the Action menu as a procedure that can be selected and run from the menu. The . causes it to be un-displayed but available for activation by buttons, data entry, a call by another procedure or some other trigger.
Just before I saw your response, by using the help docs, I figured out the field trigger of the code, just like you indicated above.
I understand that put thing dot in front of the name makes it invisible. In the future, how would one make it visible to alter it?
Is there a way, to delete unused Procedures? The Remove icon in the toolbar?
If the Procedure is to be used in only one place, ie this field, does it need to remain in the Action menu?
The dot will cause it to be invisible to the Action menu, but not to the View menu. You can still select it from the View menu, if you want to edit it.
The remove icon in the tool bar is one way. Another way is to use the View Organizer.
You can also make not show in the Action menu by setting that option in the Properties Panel:
This way there is no need for the dot in front of the name.
All my questions, up to now, are answered.
I appreciate the help from all and the promptness with which things were cleared up for me. I now understand the difference between Procedure and Formula and the use of code and functions respectively.
I need to convert a rather large FileMaker solution to PanoramaX. So, while learning about Procedures and Formulas is important, my primary task is to learn relationship creation and presentation (forms), since I won’t be changing any of the data. Are there any videos, paid or free, that any of you would recommend?
Thanks again