report debts

[Gerardo]

Hello everyone!!

I require your support to be able to find the correct expression to be able to generate a report of customer debts.

In which I show the month and the year in which I did not make said payment.

I'm honest, I'm a bit lost, that's why I go to the community to ask for their support.

Thanks

[Joseph_Seddik]

Please post screenshots of your tables and what you've done so far; this will make it easier for the community member to help you. Thank you. 

[Gerardo]

How about Joseph_Seddik!!

I will gladly upload the photos of the tables and the formula that I have used until today, which only manages to bring the last payment, from there to find out if he paid or not the current or last month, I am stuck in the formula to be able to generate said report debit and show me the month and year that the monthly payment was not made.

Greetings.

[Joseph_Seddik]

Hola Gerardo,

The expression is simple, for an Alumno record it would be:

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12} - [Related Registro Pagos][Mes a pagar]

This expression assumes that there are twelve payments; one for each month of the year. 

I noticed that you don't have "Año a pagar" column in your table, so you are only registering the month for which you receive the payment, but not the year. Does this mean that you don't expect to collect payments across two different years? If this is the case then it is OK, you have the expression. Otherwise, how are you managing this?

[Gerardo]

Hello Jose_Seddik,

I use the year from the date on which the payment is made, YEAR([FECHA])... As for the expression, I will do it and comment on the result.

Thanks

[Joseph_Seddik]

So you cannot have pending payments from December last year for example.

[Gerardo]

Correct, what I want to do is to show the months that the monthly payment was not paid and its year.

[Joseph_Seddik]

This is not what I wanted to say 🙂 Sorry, let me rephrase:

What are the first and last months that the customers have to pay for? Are you managing a school year for example always starting in September and ending in June? Or can contracts start anytime and end anytime? Is it the same for all customers? or you can have different payment durations depending on the customer/contract? 

Thanks.

[Gerardo]

All year long, it is a soccer school, for which it receives multiple payments per month (uniforms, tournaments, trips, monthly payments, etc.), I only want to identify the monthly payments, so that they indicate whether there is a debt or not.

I hope to be explicit...

[Joseph_Seddik]

Yes, thank you. My question is: let's say that I have a missing payment for February, how do I know whether it is February this year or the February last year?

[Gerardo]

Both of them

[Joseph_Seddik]

So shouldn’t you have a “Año a pagar” column? Sorry , if I’m missing something obvious. 

[Gerardo]

Of course, just start trying to do it only with the month, since there is no problem adding the year later... only I still can't do it, I'm not very skilled, that's why I ask for your support and learn a little from you The experts.

[Joseph_Seddik · Accepted solution]

No problem. For months only, the expression above will serve you. 

[Gerardo]

The formula that I share below gives me the result of the months that have not been settled monthly.

The problem I find myself in is putting a condition so that the months of the current one are seen in the previous one, only I can't do it since I don't know how I can do the segmentation between a data list and a data.

I would be very grateful if you could guide me to segment it by date and making it possible to appreciate the name of the month and not the number, thank you for your support.

[Gerardo]

I was able to solve the problem, thank you very much for the support.