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 gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Accounting Issue

Status
Not open for further replies.

neflictus

Programmer
Mar 24, 2016
2
RO
Hello

It's my first post here. I'm not much of a Fox kind of guy, but somebody asked me to help him with a problem.
Don't know how to do it, so I'll pay it forward. If anyone could help me, that would be great. If not, well, that's life and thanks for trying.

There are 3 tables:

1. Debits - with 3 fields: month, year and debit (sum of money).
2. Receipts with 3 fields: receipt_no, date, value (sum of money).
3. Outputs with the following fields: month, year, debit, receipt_no, date, value

So basically the situation is this: in debits you record the utilities for an apartment which are monthly.
In receipts you have the receipts which are proof that the guy actually pays his utilities.
The outputs table should contain records of debits being paid.

But like this:

Example 1
John has a bill for his utility in 01 (January) 2015 of 60$.
John has a bill for his utility in 02 (February) 2015 of 20$.
John has a bill for his utility in 03 (March) 2015 of 20$.

John pays the utilities in April on 02.04.2015 and he receives a receipt with no 1 dated accordingly. And he only pays 90$, leaving 10$ debit.

I'd like the output table to have these recordings:

month 1
year 2015
debit 60$
receipt_no 1
date 02.04.2015
value 60$


month 2
year 2015
debit 20$ - cause he already paid for the 60$ in January in the first record
receipt_no 1
date 02.04.2015
value 20$

month 3
year 2015
debit 20$ - cause he already paid for January and February.
receipt_no 1
date 02.04.2015
value 10$

and an empty recording with just the field Value completed with the value of 10$ which were not paid.

So basically I want the debits to be paid in cronological order in conformity with the receipts (also in cronological order).

Thanks for reading so far. I hope my english doesn't suck very bad and that I've made myself understood.






Example 2:

John has a bill for his utility in 01 (January) 2015 of 10$.
John has a bill for his utility in 02 (February) 2015 of 10$.
John has a bill for his utility in 03 (March) 2015 of 10$.

John pays the utilities in April on 01.04.2015 and he receives a receipt with no 1 dated accordingly. He pays 15$.
John pays the utilities again in April on 02.04.2015 and he receives a receipt with no 2 dated accordingly. He pays another 14$.

Output table

month 1
year 2015
debit 10$
receipt_no 1
date 01.04.2015
value 10$


month 2
year 2015
debit 10$
receipt_no 1
date 01.04.2015
value 5$


month 2
year 2015
debit 5$
receipt_no 2
date 02.04.2015
value 5$

month 3
year 2015
debit 10$
receipt_no 2
date 02.04.2015
value 9$

and an empty recording with just the field Value completed with the value of 1$ which were not paid.


He should cover the debit of each month (in cronological order) using payments which he made (in cronological order - receiving receipts in return) and move to the pay the debit for the following month after he paid in full for the first one.
 
It should be fairly simply to understand the mechanics of this, so I'm confident you'll get help here.
I have little to do with accounting and while this stuff surely is not over my head, it's boring me, which is a dangerous thing to develop valid code.
Roughly judging the concept, I'd say you have some processing leading to an output, but then would either need flags in the table for processed records or would flush out data into archive tables after each go.
Anyway, accounting should also keep track of every single transaction to make everything verifiable. Things like a balance of debit ore credit are actually no data, but are derived from all transactions.

Anyway, I don't have the mindset right now to come up with some code, but it'd be wise to first ask anyway: Is this just the outline of an idea or is there already code existing needing a fix or refactoring?

Bye, Olaf.
 
Welcome to the forum.

For your information, the requirement you have described has a name: open-item accounting.

It's perfectly do-able, but there is no single command or a simple bit of code that we can give you to achieve it. It involves writing some moderately complicated logic, which is not something we normally do in the forum. Having said that, it's possible somebody will give you an outline of some suitable logic, which you might then code yourself.

By the way, I question your requirement for an "empty recording". I think you mean a record which contains a debit amount, but no date or receipt number. As far as I can see, that doesn't serve any useful purpose, as the outstanding debit can easily be derived from other records.

Also, all three tables seem to be missing an essential piece of information: the identification of the tenant (John, in your examples).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
The tenant is not important right now. I kept it simple.
I'm sorry to hear this. Like I said, I'm not much of a Fox guy.
 
Please don't be discouraged. My point was that, to solve this problem, you really need to write some non-trivial code. To do that, you will need a better knowledge of VFP than you apparantly already have. So you are going to have to sit down and learn the fundamentals of the language. If you are expecting us to write the code for you, that's not what the forum is for.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Well, I come back to my question: Is this just the outline of an idea or is there already code existing needing a fix or refactoring?

If you have things at hand already, it would not be so hard to adjust this. And if this is just an outline idea, you could implement it in another language you're more capable to do.

Bye, Olaf.
 
Welcome to the forum.
We will be glad to try to advise/suggest/assist you as to how to approach your code issue.

Please keep in mind that we aren't here to just GIVE you code, but instead to help you create your own code. One reason for that is that there are typically 6 or more ways to accomplish any single task so you need to find the one that works best for you.

"but somebody asked me to help him with a problem"
Olaf has asked the question already, but I'll repeat it.
* Does that mean that an application already exists, and that 'somebody' wants you to modify it?
* Or does that 'somebody' have nothing and wants you to create it for them?

A couple of suggestions...
Since you say: "I'm not much of a Fox guy." I'll first suggest that you spend some time looking at the Online Free VFP Tutorials at: Once you get that understanding of the basics, you will be better able to implement the suggestions we might be able to offer.

Next I'd suggest that you start small and grow your project from there.
If the issue is the modification of an existing application...
* then what does exist?
* what specifically is needed to be modified?

If the issue is to create a totally new application for them...
* Begin your efforts with small pieces - maybe just create a user entry screen and get it to write the data where you need it to go.

Good Luck,
JRB-Bldr
 
One more thing regarding something that is not unique to Visual Foxpro:

There are 3 tables:
1. Debits - with 3 fields: month, year and debit (sum of money).
2. Receipts with 3 fields: receipt_no, date, value (sum of money).
3. Outputs with the following fields: month, year, debit, receipt_no, date, value

If this is an existing application you have to work with what you have.
But if this is to be a New application, you might want to think about Data Table "Architecture" and if the tables support Data Normalization.

Most accounting systems that I have worked with do not have separate data tables for Debits & Credits/Receipts.

And 'Outputs' are generally not a permanent separate 'table'.
When an 'output' is needed it is done via a VFP Report which uses a temporary table/cursor which most often is deleted when done. This is generally because each VFP Report may have its own separate data requirements and therefore only needed when that report is generated and not needed any other time.

Good Luck,
JRB-Bldr

 

If you are not capturing either the apartment number or the tenant in any of the tables does it mean there is only one apartment for which the report is required? If there is more than one apartment, without the apartment number or tenant name it would not be possible to create the report.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top