I'm new to FM6 but learning fast. I have a Payments file which I need to display in a User layout the latest payment entry (based on date) and the latest payment amount based on that date. Max() works great for the date, however I'm stumped on how to display the Amount from the record that Max() identifies. Last() works IF all my records are entered sequencially by date but if for some reason (trying to idiot-proof this) a late entry is made of an earlier date, then my LastPayment display shows that amount vice the latest date amount. Please educate me!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Determining latest payment entry
- Thread starter jimb51
- Start date
Well, this may not do what you need, but you can sort a portal of related records. Would it be possible to construct a one-row portal and sort by date, descending? It sounds like this is the sole purpose of this display area, so you aren't likely to want to sort it any other way. Maybe I'm wrong on that point, but this could be a real quick way to solve your problem.
You define the sort order in the relationship definition, by the way.
If my suggestion isn't complete enough, or it fails to address your need, please post a follow-up. Conversely, if that works, please post that as well to help future users who are searching the archives for answers.
Good luck!
You define the sort order in the relationship definition, by the way.
If my suggestion isn't complete enough, or it fails to address your need, please post a follow-up. Conversely, if that works, please post that as well to help future users who are searching the archives for answers.
Good luck!
- Thread starter
- #3
The portal I have now is as follows:
Company/LastPayment/LastPaymentDate/RemainingBalance
A one line portal won't work here due to multiple companies, though it may work in another form where I display the last payment. I tried sorting on Date in this portal then tried to grab the Last(Payment) but that didn't work when I manually entered a pre-dated payment. It still displayed the last physically entered payment. Does FM6 use record pointers similar to other DBMS where once a record is pointed to, any reference to a field comes from that particular record? My final solution may be to force the Date to be the current date. Back to ya'll!
Company/LastPayment/LastPaymentDate/RemainingBalance
A one line portal won't work here due to multiple companies, though it may work in another form where I display the last payment. I tried sorting on Date in this portal then tried to grab the Last(Payment) but that didn't work when I manually entered a pre-dated payment. It still displayed the last physically entered payment. Does FM6 use record pointers similar to other DBMS where once a record is pointed to, any reference to a field comes from that particular record? My final solution may be to force the Date to be the current date. Back to ya'll!
JeanW, which two fields would you use in a self-join? I'm sorry to be dense, I just couldn't work out how that would help in this situation.
Jimb51, you listed a series of fields (Company/LastPayment, etc..) as your portal. You mean these are the fields you are displaying in the portal, right?
My suggestion, though I hadn't anticipated multiple companies being presented in the portal, was to define a sort order for the payments based on LastPaymentDate, not record-creation date. Is that what you tried? That would put the latest at the top, by the date payment was credited, which seems to be what you are going for.
Just to be clear, I don't mean to use a sort command. I mean, in Define Relationships..., select this relationship, edit, check the checkbox for 'sort related records', specify your LastPaymentDate field, descending. If that fails, then I'm not understanding your question or your data.
As far as pointing to related records, yes, if you have a scripted process that says 'go to portal, go to portal row 5, set/get field value of related field x' then yes, it is working on the 5th related record. Was that your question about pointers?
Back at you!
Jimb51, you listed a series of fields (Company/LastPayment, etc..) as your portal. You mean these are the fields you are displaying in the portal, right?
My suggestion, though I hadn't anticipated multiple companies being presented in the portal, was to define a sort order for the payments based on LastPaymentDate, not record-creation date. Is that what you tried? That would put the latest at the top, by the date payment was credited, which seems to be what you are going for.
Just to be clear, I don't mean to use a sort command. I mean, in Define Relationships..., select this relationship, edit, check the checkbox for 'sort related records', specify your LastPaymentDate field, descending. If that fails, then I'm not understanding your question or your data.
As far as pointing to related records, yes, if you have a scripted process that says 'go to portal, go to portal row 5, set/get field value of related field x' then yes, it is working on the 5th related record. Was that your question about pointers?
Back at you!
- Thread starter
- #6
Kertusa. First off, thanks for spending so much time on this problem! You did answer my question with respect to pointers. I'll have to work on how to use scripting to get to the particular record for Max(LastPaymentDate) and to Get(LastPaymentAmt) from that record.
As far as my underlying problem, here is more detail on what I'm trying to do:
I have 3 files, DEBTORS,ACCOUNTS,PAYMENTS. Each Account represents one debt to one company by one Debtor. A Debtor can have many payments to many companies in the Payments file. Currently, when I display a Debtor, I show his vitals and a portal to Payments which displays a summary of his payments to each company, hence the fields I mentioned above. I was just trying to ensure that the LastPaymentAmt actually matched the payment of LastPaymentDate.
Don't spend an awful lot of more time on this unless there is an easy fix.
As far as my underlying problem, here is more detail on what I'm trying to do:
I have 3 files, DEBTORS,ACCOUNTS,PAYMENTS. Each Account represents one debt to one company by one Debtor. A Debtor can have many payments to many companies in the Payments file. Currently, when I display a Debtor, I show his vitals and a portal to Payments which displays a summary of his payments to each company, hence the fields I mentioned above. I was just trying to ensure that the LastPaymentAmt actually matched the payment of LastPaymentDate.
Don't spend an awful lot of more time on this unless there is an easy fix.
@Jimb51 - last thing I would say is that you're dense - not at all ¡¡
A self rel. on f.i. the name and that in a portal, can give the name, date, single amount, max amount and max over all the records.
Sorted in the way you say can give the needed info....
as far as I understood the problem in a hurry
A self rel. on f.i. the name and that in a portal, can give the name, date, single amount, max amount and max over all the records.
Sorted in the way you say can give the needed info....
as far as I understood the problem in a hurry
- Status
Similar threads
- Locked
- Question