FM - Best Way of Moving Through List of Property Codes
Instead of a monthly aging report of all tenants late on payments, boss wants a daily report selecting one property at a time. I just realized I was making this way too difficult by looking through active properties, saving the PropCode to a field in a one record table, then finding the next PropCode in order the next day. However, isn't there a way of simply looking through a List of Prop Codes via a Active Prop link to the Tenant table? Problem is, I can't simply save a number and count down to the next property the next day because we add a property about once per month. So, the list would be sorted by Property Code and I would just take the next value in the list. I don't see a "next value" function like the next record function, but I'm sure there's another way of doing it. Am I on the right track?
ASKER
That sounds like you would do it on the actual property record instead of my second idea. Couldn't I do it on a list of prop values from the Tenant table? The trick is just finding the next value in the list. Also, I'm open to persuading the boss to go by record #, which is the same as date. That would apply to the value list as well.
Actually, GetNthRecord is generally done on a related set of records, whether or not those records are on-screen in a portal, rather than a List. If the relationship will give you the list you are looking at (and relationships can be sorted), and you know the Record Number in that sorted related set, then you can use GetNthRecord to calculate RecordNumber + 1. But it also sounds like I don't have enough information and am making this more complicated than it needs to be... If you can list, in order, the steps that are being taken and what you want to see that might help. As I hear it from you:
- Search for list of Past Due Properties
- Select a Property to View
- Retain Property Code for next day
- Run routine that goes to Property Code from previous day and then displays NEXT on in sorted list.
That's what I understand from what initially said.
ASKER
Yes, pretty close, except that we're using the property code to search in a particular tenants layout that shows aging info. Thus, the find of the next property code would need to be done via the linked table, either via a value list or I guess a related set of records, as you say.
Nothing attached....
ASKER
Trying again. It's a jpg.TntAgingReportScript.JPG
So what about this...whenever you run the report, just save the Property Code for the viewed record. Next time you run the report, after doing the initial Find, just do a loop that looks for the saved Property Code and then goes to the next record after that and records it as the new code?
The main problem I can see with this is that if someone falls off the list, presumably because they paid, then you won't find them by Property Code AND if you use the Record number method that will be off as well... I'm sure there is a reason for this "see only the next record in the list the next day" approach but it really feels problematic to me if the list could change from day to day...
ASKER
No, the list has nothing to do with TntAging, it's directly from PropInfo table itself and in order of Purchase Date, so any purchases won't affect it. A sale might cause a problem, so have to think of that; maybe storing the list in a static field. Generally, I don't like looping, etc., if I can help it cause it takes processing time from FM.
There is a function called "GetNth record" which might be what you are looking for. However, from your description I'm uncertain about whether it's what you really want. If you're adding Properties and the list is sorted by Property Code rather than date, the "next" record might not be the same one it was the previous day or is someone paid and was removed from the list that would also alter the meaning of "next" record. Using GetNthRecord, if you know the Record Number of a record in a related set - presumably from the previous day, then you can calculate forward or backwards in the list from that record. But as I say, if the related set can change from day to day, then the record number from the previous day is meaningless.
I suppose that if you had the previous days Property Code, you could find the Record Number of that code in the list using a PositionValue () function and then calculate the Nth record as that position + 1
https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/getnthrecord.html