I am working in a 2.01b Database. I want to show the last transaction date from the Customer Ledger Entry table for each Customer on the Customer Card. I am using a flowfield but I need the data in Descending order with the last date showing in the field on the Customer card. Thank you for any ideas.
Are you using a Lookup CalcFormula? Have you tried the Max method instead?
Yes I am using the CalFormula property for the flowfield I have created. I tried the Max method. It worked but it was extremely slow…to slow. I am looking for an alternate solution.
IMHO it would be easier to add a couple of lines of code and “FIND” the entry you are looking for. Form 302 contains the code on how to achieve this: CustLedgEntry.SETCURRENTKEY("Customer No.","Posting Date"); CustLedgEntry.SETRANGE("Customer No.","No."); IF NOT CustLedgEntry.FIND('+') THEN CLEAR(CustLedgEntry); Place this code in the OnAfterGetRecord trigger in the form, place a textbox with "CustLedgEntry.“Posting Date” on your form and that’s it. Saludos Nils
That is exactly what I ended up doing. I was looking for a way to do it with a flowfield in a table…something more elegant and to have the lookup. Thanks for the ideas
quote:
I was looking for a way to do it with a flowfield in a table… something more elegant
you got me there… though I don’t think it is possible to find the “last” entry with a flowfield - please correct me if i am wrong…
quote:
to have the lookup
that one can be fixed, just place the following code on the OnLookup trigger of your field: CustLedgEntry.SETCURRENTKEY("Customer No.","Posting Date"); CustLedgEntry.SETRANGE("Customer No.","No."); IF CustLedgEntry.FIND('+') THEN FORM.RUN(0,CustLedgEntry); (that should do, not tested though…) Saludos Nils
quote:
you got me there… though I don’t think it is possible to find the “last” entry with a flowfield - please correct me if i am wrong…
Originally posted by nilsm - 2004 Dec 03 : 09:04:23
You can use the Max Method and it will give you the latest date. The speed issue was probably with the Keys. The Flowfield chooses a key which is sometimes not the best. You would need to make sure there was a key with Customer No., then Posting Date (and hope it uses that one [B)]) Another (more elegant?!?) option would be to create a Function on the Customer table (instead of using the OnAfterGetRecord). You could then add this function to the form as the SourceExpression of a textbox as if it were a field in your table.
the slow performance using the flowfilter with Max sounds like a key issue. did you match the key you are using with the fields you are filtering? I get results quickly on an SQL database with over 71,000 customer records and 4,300,000 customer ledger records.
The flowfield of type Max and Min was made a bit strange back in 2.01. It always used the first key it could find starting with the field to find the min/max value of. I don’t remember exactly when it was changed, but I think it was in 3.0.
Remember that you can still get the Lookup functionality. You just have to code it in the OnLookup trigger of the TextBox control.
quote:
You can use the Max Method and it will give you the latest date
[Oops!]… somehow I missed the most obvious solution - of course the date of the lastest transaction is also the largest date. Sometimes the most simple solution is too obvious. Thanks for the hint [;)] Saludos Nils
Another solution might be to have a plain old date field on the Customer Card whose value gets set to the appropriate date by the posting routines which create the Cust. Ledger entries. You could also change it whenever you modify the Customer Card itself. This might be useful if you have ever have occasion to enter erroneous Cust. Ledger entries: even after you void them out the bad entries would still be picked up by the FlowField maximum lookup, but you might be able to put the correct date back on the Customer card.
I’me not sure if you can do this in such an early version, but can you have a flowfield with a Method of Min or Max. With the correct filters I belive you could get this to show the last posting date.
Hi, What’s the property to set the Max method on flowfield?
Nevermine… Apparently, I’m blind… [B)]