Jump to content

Does Filemaker handle Relative Fields?


This topic is 6302 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

I am trying to switch from Excel to Filemaker. For most static databases, it is going well. But one VERY simple Excel database is giving me trouble. I need to perform a calculation based upon a previous record. In this database, the first record is "Beginning_Balance", and the last record is "End_Balance". So for record #2, its value for "Beginning_Balance" will be the value of "End_Balance" from record #1. In Excel, this is very easy, due to the use of relative field reference. But I cannot seem to find the equivalent in Filemaker. I am very new to Filemaker...so it may just be called something different...but after scouring the "help" files, I just can't find it. Any help would be vastly appreciated.

Many Thanks,

--Louis

Link to comment
Share on other sites

Since you're on fm7 and not fm8+ could it be done this way:

http://www.filemakerpros.com/LULAST.zip

But although it gives you a solution, should you consider if the reasoning in pulling a database app. out of it's realm is justified. Be carefull not to rub prejudice into a tool you're newbe to, you need to explain why you have thrown your love on a database tool?

--sd

Link to comment
Share on other sites

Hi

it seems a very easy task for FM7, using a variable to store the End_Balance before to create a new record.

So all you need is a script to make a new record with these steps:

Set variable[$storeEB = YourDB::End_Balance]

New record/request

Set field [YourDB::Beginning_Balance ; $storeEB]

Link to comment
Share on other sites

Daniele, when was $variables introduced??? With fm8 in my humble opinion! The way we dealt with it back then, was to make the script recurse, to avoid buttons with the scriptparamter tied to it ...look above!

--sd

Billede_1.jpg

Link to comment
Share on other sites

  • Newbies

Hmmm...a running summary...interesting idea...but since one field is at the left of the table ("Beginning_Balance"), and the other is at the right ("End_Balance"), I think I'd still be running into the same problem...? Maybe I'm missing something.

re: Soren: Yes, I had thought about questioning the logic of taking Filemaker and trying to make it act as a spreadsheet tool...but its layout function is SO much better than Excel's garbage: I really prefer it for printing statements and reports that are customized from the data (in this case, Mortgage Statements that will be sent to customers...so it is important that they look nice, as well as be accurate). Excel is just too poor in this layout area...especially for someone who is used to using Adobe InDesign to create nice-looking documents for every other aspect of the business. Also, I find Filemaker to be much more reliable than Excel (running on Mac OS X); and with all of the relational database capability in Filemaker, there are so many other things I will be using Filemaker for (rather than using Access on a PeeCee ), that I'd just love to dump Excel altogether. I've even considered installing "Star Office" under X11 on my Mac, just to avoid MicroSloth Excel. ; ->

In this case, though Filemaker doesn't have the "relative reference" capability of a spreadsheet program like Excel...even if I had to hard-code each and every record in some way to reference the record above it using absolute reference ("table::cell" type of thing?), it would be worth it, as I would be able to use Filemaker rather than Excel. I used to use FileMaker extensively back in the 1.0/2.0 days, and LOVE it compared to Excel. And now that it has relational capability, I'd like to replace Excel AND Access, meaning that I'd never have to go to my PeeCee! ; ->

If only Filemaker allowed you to embed formulas/references into individual cells like Excel: now THAT would be the best of all worlds! I don't know why anyone would ever use Excel at that point...

Link to comment
Share on other sites

Filemaker does allow you to reference "individual cells" - they are called "fields in related record". But you need to establish a relationship first (in version 8 and higher, you can also refer to Nth record of the found set - without a relationship).

However, such references can slow down a solution - especially in list view with a cascading relationship. That's why Søren suggested looking up the data from the previous record. On the other hand, a lookup will not update automatically when related records are modified.

since one field is at the left of the table ("Beginning_Balance"), and the other is at the right ("End_Balance"), I think I'd still be running into the same problem...?

If you want to show the previous balance, you could subtract the transaction amount from the running total (in the same record). Note however, that a running total summary requires all relevant previous records to be in the found set.

Link to comment
Share on other sites

Another dynamic method (adjusts for deletions) is to create a relationship using the ">" operator, using any field that is sure to be greater than the record before. This works well for smallish relational sets, such as the balance for 1 person. It is pretty slow if you use it for the whole table, as you're using a compound relationship with a relational operator and relational calculations.

Link to comment
Share on other sites

This topic is 6302 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.