Jump to content
Daglasov

Trying to display current total of unpaid invoices

Recommended Posts

Posted (edited)

Amateur hour here. I've been messing around in FM for years but only for personal use. Now I want to do something I thought would be easy to programme with my knowledge but I got stuck and can't seem to get unstuck.

I have an Invoice database.

My regular clients have a payment delay.

Some of them are late of course.

Every payment is filed so every invoice from the past has a calculation showing Late: Yes or No.

I want to display on every new invoice the amount of money that client owes based on due invoices at that moment displayed as a simple sum.

I did manage to do this through a portal but only managed to display a list of unpaid but due invoices with the sum adding up through the rows. But this is not what I need, it's to big.

I need a nice simple single number of the sum they should pay that I can incorporate into a textual calculation and display as a text message on the invoice.

Also, I tried to get a sum of related records and relate the client serial number and Late: Yes field but it seems you can't use a Calculate when needed field in a relationship since it keeps saying the relationship is wrong.

C'mon guys and girls, do your good deed for the day and help a newbie out.

Kind regards from sunny Croatia,

Dag

Edited by Lee Smith
Removed all of the white space

Share this post


Link to post
Share on other sites

I suggest you create number fields in the client table for current balance, Over 30 Days Due, Over 60, etc., and update them with a script as payments and invoices are processed. Stored balances make it easy to the kind of thing you want, and it will keep your system from getting slower and slower as you add more records.

Share this post


Link to post
Share on other sites

Hmm... I don't like the idea of using a script for this to much. It seems to me it shouldn't be much of a problem to do this since all the infrastructure is there to provide the result, I see it right there within the portal, I just can't get it out to use within a sentence... [emoji23][emoji23][emoji23]

I would just like to do a nice and simple sentence by the end of every invoice like this:
"According to our records, at this time you are > due!"
Of course in Croatian, don't mind the grammar...

Thank you for trying to help! [emoji6]

Share this post


Link to post
Share on other sites
1 hour ago, Daglasov said:

I don't like the idea of using a script for this to much.

The problem with doing this through relationships is that the solution will get progressively slower as the number of client's invoices and payments increases.

 

1 hour ago, Daglasov said:

I see it right there within the portal, I just can't get it out to use within a sentence...

I am afraid I wasn't able to follow your description of what you have so far.

 

See also:
http://fmforums.com/topic/76398-allocating-payments-to-invoices-questions/

 

 

Share this post


Link to post
Share on other sites
9 hours ago, Daglasov said:

I did manage to do this through a portal but only managed to display a list of unpaid but due invoices with the sum adding up through the rows. But this is not what I need, it's to big.

Can you clarify what this portal is displaying vs. what you want to display? Feel free to attach a screenshot.

Share this post


Link to post
Share on other sites

Ok, Screenshot 1 is how the invoice looks like, just to give you an idea of it.

You see the three red lines by the end, I made a portal here to show three or less invoices that are due. This works fine but can only fit so much.

Now I'd like to delete that portal and in its place put the sentence I talked about, with the total debt owed. To stress the problem further.

Screenshot 2 shows you a portal I made on the side, that has enough rows to show all the unpaid inovices. It has a summary that adds up to the total debt owed by the customer in the lower right corner. If I could make this summary total stay on one place somehow, I could use it for my purpose but it keeps moving since different clients have different number of unpaid invoices and I can't sort it to keep the total on top.

I'm not worried about the slow down you talk about since it is a small association, it has had 1000 invoices from 2009 'till now.

As I said before, I'm stuck and can't seem to find a simple automatic workaround...

1.png

2.png

Share this post


Link to post
Share on other sites
1 hour ago, Daglasov said:

Now I'd like to delete that portal and in its place put the sentence I talked about, with the total debt owed.

I think you could replace the portal by a text object and merge the summary field (defined in the portal table) into it. This assumes the portal has no filtering applied to it. I am mostly guessing here, because you did not explain what exactly the portal is showing and how the underlying relationship is defined.

Share this post


Link to post
Share on other sites

I'm sorry, the structure is complex so I was mostly speaking generaly and hopeing someone would know what to do.

The portal is showing data from another instance of the same table, "Invoice 2" related to this one by the customer serial code and filtered by the calculation Invoice 2:: Late="Yes"

I have a calculation in every record of the Invoice table that calculates whether an invoice is paid or not, giving a Boolean result...

Share this post


Link to post
Share on other sites
Posted (edited)
4 minutes ago, Daglasov said:

The portal is ... filtered by the calculation Invoice 2:: Late="Yes"

Then you will have to either change the relationship so that it includes the filter, or place the text object inside a one-row portal filtered the same way. Or define a calculation field that returns the invoice's sum when the invoice is late and sum/summarize it instead.

Edited by comment

Share this post


Link to post
Share on other sites

Initialy, I thought I could relate a new instance of the table, Invoice 3. Relate it to this one using Customer serial code and Late=Yes, giving all records a bogus field that says Yes so that I can relate it to the Late=Yes field.

Then use a calculation Sum (Invoice 3::Summary of totals) to get this total.

It seemed logical to me. But it didn't work out. Don't know if you get my mumbling here... :)

1 minute ago, comment said:

Then you will have to either change the relationship so that it includes the filter, or place the text object inside a one-row portal filtered the same way.

How can I add a filter to the relationship? If I do a one row portal with this filter it just sums up one total instead the sum of all late invoices... Or am I doing it wrong?

Share this post


Link to post
Share on other sites
Posted (edited)

How exactly is the Late field defined? If it's an unstored calculation (as it probably must be), then it cannot be used it as a matchfield in the proposed relationship.

 

Edited by comment

Share this post


Link to post
Share on other sites

Yes, if you look up, I already stated that. That's why my initial plan did not work.

The Late field is a calculation taking into consideration Due date, Current date and a Tick box one ticks when they pay a specific invoice. Resulting in a Yes or No. It is set as Recalculate when needed or it wouldn't go into Late=Yes when the Due date passes... :(

Share this post


Link to post
Share on other sites
28 minutes ago, Daglasov said:

Yes, if you look up, I already stated that.

No, the words "unstored" or "current date" do not appear above my previous post. Anyway, I already gave you two ways to do this with your current relationship. If you want to define a new relationship, then explain exactly how the Late flag is calculated - including how any calculation fields it depends on (such as DueDate) are calculated.

Share this post


Link to post
Share on other sites
10 minutes ago, comment said:

No, the words "unstored" or "current date" do not appear above my previous post. Anyway, I already gave you two ways to do this with your current relationship. If you want to define a new relationship, then explain exactly how the Late flag is calculated - including how any calculation fields it depends on (such as DueDate) are calculated.

You are right, Unstored does not appear but Calculate when needed does...

You gave me two options:

1. Then you will have to either change the relationship so that it includes the filter

- To which I asked: How can I add a filter to the relationship?

 

2. Place the text object inside a one-row portal filtered the same way

- To which I asked: If I do a one row portal with this filter it just sums up one total instead the sum of all late invoices... Or am I doing it wrong?

 

I have no problem creating a new relationship if it helps. Here are the details you asked for.

Fields in use:

Paid (Yes or No tick box set default to No. User clicks Yes when Invoice is paid)

Date the Invoice is created (Record creation date)

Date the Invoice is due (Date the invoice is created + Payment delay in days)

Payment delay in days (usually 30 or whatever)

Due date passed ( If ( Date the Invoice is due - Get(Current date)  < 1 ; "Yes" ; "No" )

Late ( If ( Due date passed="Yes" and Paid="No" ; "Yes" ; "No" ))

(I left out a few od those get as number for dates to make things easier to read... In every case, this whole system works.)

 

Thank you for taking the time and patience to go through this for me...

 

Share this post


Link to post
Share on other sites
3 hours ago, Daglasov said:

If I do a one row portal with this filter it just sums up one total instead the sum of all late invoices...

Make sure the summary field is not defined as running total. Otherwise it will always show the value of the first related record, no matter what you do.

Please note that I have added an alternative method to handle this, also using your existing relationship.

 

3 hours ago, Daglasov said:

Payment delay in days (usually 30 or whatever)

This is crucial. If every invoice can have its own "delay", stored in the invoice itself, then the task of creating a relationship that shows only invoices past their due becomes even more complicated. I won't go into it now - if you like, do a search here for "Ugo's method".

Share this post


Link to post
Share on other sites
17 hours ago, comment said:

Make sure the summary field is not defined as running total. Otherwise it will always show the value of the first related record, no matter what you do.

Please note that I have added an alternative method to handle this, also using your existing relationship.

 

This is crucial. If every invoice can have its own "delay", stored in the invoice itself, then the task of creating a relationship that shows only invoices past their due becomes even more complicated. I won't go into it now - if you like, do a search here for "Ugo's method".

By God Mr Comment, you did it! :)

The thing blocking me was the Running total. Once I removed that, there was no problem creating a portal and creating a sentence within do display the total.

It is conditionaly formated to not be visible on the invoices whose clients have no debt and to display an alarming message for the clients that have debt.

Very pleased with the end result, I thank you for helping me and declare this issue closed for me!

 

 

 

 

WithDebt.png

WithoutDebt.png

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.