emerywang

Great. Thanks. BTW, would it be any different if I used an "If" function instead of Case? Never could figure out what the difference was. Thanks. -Emery

Hi. I have a field that's a number, with an Auto-entered calculation. How can I get it to update, whenever the data that it's making the calculation on changes? I know if I changed it to a calculation field, it would update. However, it would also replace all previous values in that field, and since we used a different calculation formula in the past, that would change all our old numbers, and we can't have that. Thanks for the help! -Emery

Thanks, I'll try that. BTW, I've only activated scripts thru the menu, or via a button. Is that what you're thinking of, or is there a way for this script to automatically run? thanks for your help. -Emery

Hi. I have created a running counter to keep track of which record I'm on. Since my file contains multiple account numbers, I first created a self-joined relationship called "self," where account number =::account number. My counter, then, is a number with an auto-entered calculation that looks like this: Count(self::account number) It works fine while I create new records. The counter dutifully enters the correct number of the record I'm on, after I enter the account number for the new record. So the 5th record with account number "3443" would have a counter value of 4, while a new record with a new account number yields a value of 0 (I know it doesn't start at one, but that's okay). It gets messed up, however, if I delete a record, or change the account number of that record, because the counter does not dynamically change. So, is there a way to create a counter that dynamically updates if I change the account number, or if I delete a record? Thanks. -Emery Ps: a calculation field won't work, because it dynamically updates all instances of that field, so it gives me the total number of records on each page. What I need is a running counter, not a total counter.

Got it. Thanks, man! -Emery

Thanks, J, for the quick reply. I considered doing this, and I can create relationships based on the salesman's ID number and a corresponding date or counter. But the part I am missing, is how to make that relationship correspond to the latest records, given the latest records may not have the same creation dates or counter values. For example, the latest record for account #1 may have been created last week, whereas the latest for account #2 may have been created yesterday. Have I overlooked something? thanks. -Emery

Hi. How do I define a field that totals the fields in the latest (newest) of multiple records? For example, say I have several records for each of account numbers 1, 2, 3 and 4. Under each account number, I have salesmen A, B and C. What I need, is a total of salesman A's commissions, for example, but only in the latest records that he shows up in. So if salesman A is in records with account numbers 1,2, and 4, then I need the total of his commissions in the latest of those records. I guess the thing I'm missing is the command that says to look for something in the latest record it shows up in. Thanks in advance.

Thanks, Jerry and everyone. I actually used a modified version of this, with an "If" function, and it works. But you guys put me on the right track. Thanks again. -Emery

Thanks, everyone. Below is the calculation. Where I have (no data) is where data does not exist. This is a rate calculation, and looks at client accounts over time. Data will be returned if the account is old enough to contain data, but not if the account isn't old enough. It is for the accounts that aren't old enough, that I get the question marks in the calc field. So what I need is some way to tell the calc field to just return nothing, rather than a question mark, if no data is available. Thanks. ((no data) /(no data)) ^ (1/10)-1

Hi. I have a calculation field, and when data is not available, it returns a question mark. How do I tell FMP to leave the field blank, when there is insufficient data to make that calc? I tried turning it into a number field, and lookup the calc. That allows me to manually delete the question mark out of the field, but that doesn't seem like the best solution to me. Any help is appreciate. Thanks! -Emery

Actually, I got it to work, thanks to your idea, Phil, and Queue's suggestion. Much appreciated! -Emery

Thanks, Phil. Yes, that will give me the number of records, but I need a running total, rather than a summary total. I need to know what the number of the record is, that I'm looking at. This is because we have an interest rate calculation we have to run, and one of the required variables is the number of months that have gone by for that particular record. Since we create one record per month per account number, I need some sort of counter that restarts for each account number, and returns a different running total number, depending on which record I'm in. So record number 4 would say "4," and record number8 would say "8", etc. Any ideas on that? thanks. -Emery

Hi. Does anyone know how to calculate the number of the record you are on, without doing a sort? For example, every record I create has an account number and date. So if I create a new record with account #101, for example, I need a counter that says "6", if this is the 6th record with account #101. Similarly, if I create another record, say #102, and that is the 3rd record with account number 102, then the counter should return "3" here. Any idea how I would do that? thanks! -Emery

Nevermind, I figured it out. The lookup function with a self join, sorted by date in descending order did it. Thanks. -Emery

Hi. I have a file of records with account numbers. Whenever a new record is created, it is assigned an account number. What I want to do is, whenever a new record is created, to have the data from a field of the previous record with that account number to be automatically entered. So for example, if I make a new record with account number 101, I would like the "price" field to auto enter the price data from the previous record with account #101. Anybody know how to do this? I guess it would involve sorting the records by account number, and grabbing the data from the corresponding field of the previous record. I'm thinking this requires some sort of self join, but can't figure out how to do it. Thanks.