You can set the portal to show only one row and to be ordered by payment date from oldest to most recent one.
That way I will not be able to see payments from other people though!
I guess thats filtering the portal, but I don't know how I can get something that results in a Bolean!
Create a self-join by customerID and a calculation, say, isOldestPayment? like payments::Date = Min ( payments__selfJoin::date ), and filter on that.
Sorry you lost me here.
My relationship is
Reminder = CustomerPayment = Customer
Would I add the selfjoin to this or am I just making new Tableoccurances with Customer = Customer.
Where would I create the Calculation?
Sorry, I guess I answered to the title of your post instead of replying to its content.
you should have :
1) a Bill table (Billl primary key: BPK)
2) a Payment table (Bill foreign key : BFK) with fields such as PaymentDate, AmountPaid
3) a Clients table connected to the Bill table
4) a relationship Bill_»_Payment connecting BPK to BFK
5) a BillNotYetPaid calc field in (1), BP defined as ABS((BillTotal - Sum (Bill_»_Payment::AmountPaid)) > gTolerance with gTolerance set to, let's say, 1.
6) a LastPayment calc field in (1), defined as Max(Bill_»_Payment::PaymentDate).
7) a ContactCustomer calc field in (1) defined as (Get(CurrentDate) - LastPayment > 30) * BillNotYetPaid
8) a ProblemIndex field in your client database (3) defined as Sum(Client_»_Bill::ContactCustomer)
Now you go on a Clients layout which displays phone number and unpaid bills numbers, you find all records having a ProblemIndex > 0, sort by it Descending in order to have the most problematic clients first and... grab the phone.
Or sort the relationship and just show the field (no portal).
Wow, that is genious!
Only one Problem:
For some reason my calculation field doesnt recalculate.
So if I have an older payment and mark that as payed it doesnt recalculate to the next newer unmarked payment.
I checked the storage options but they are set to recalculate if needed. Can I somehow trigger a recalculation?
Wow, that is genious!
Not really …
The calc field re-calculates as needed (you can't index it anyway, since it references fields from other TOs), but you also need to update the portal display.
(And – not least – you must calculate the oldest date from a relationship that includes the payment status!)
If I look at your solution it makes sense...
But I have trouble translating it into my solution.
To fit into the endsolution I am working on different tables, Also I am not using a cortesian because I made a calc field that will say if the field has to be reminded or not///
Maybe you can have a look where it hangs! I tried all sort of things.
Remindertest.fmp12.zip 4.3 MB
1 of 1 people found this helpful
[…] But I have trouble translating it into my solution. To fit into the endsolution I am working on different tables, […]
Of course you are. But you should be able to adapt the two basic ideas to your setup.
One thing that definitely prevents your posted file from working is that even though you created the self-join, your calc field has a missing field reference (which btw. is marked in demonic red and should be pretty easy to spot …).
Correct that and see what happens. Then try to work the reminded status into the relationship, so you get the oldest date only of all non-reminded records for a client.
One thing to do if something doesn't work: go back to the basics, e.g. show a list view of the CustomerPayments table, and check the calc field's value directly, then tweak the relationship and/or calculation field until you get the expected results. Only then start building the portal. The more layers of abstraction there are between your views and the raw data, the harder it is to see why something doesn't work (which it rarely does at the beginning).
1. Set up your relationship
2. Define your calc field; display a set of data (e.g. all payments of a client, some reminded, some not) and twaek 1. and 2. until if the calc gives you the correct results (so the oldest non-reminded payment record of a client has a 1, all others a 0)
3. Set up your portal, including the filter calculation, in which you can now use your functional calculation.
4. Eventually, make the portal usable (scripts, toggles, checkboxes, summaries, etc.)
All with the benefit that you have my sample file to peek into …
Thanks, I found the problem!
Because I had the tickbox not resulting in a Bolean, but just in a letter it was hard to get the relationship established...
For now I just created another field cal field that is 0 when its paid and 1 when not and I established the relationship with the reminder key that is 1 if its not payed.
So with that the calculation for the filter only looks for field that need to be paid and are the oldest.
Now I just have some extra steps that I will look at next week, fix and delete them!
Thanks for bring me on the right path!
This is a good solution, thank you!
But in my case I didn't look for people who haven't paid most bills, just for who's oldest nonpaid bill wasn't dealt with. Thank you though! I will definately use that in future solutions!