quote:

Originally posted by mark123:

quote:

Originally posted by xantom:
What language are you going to write the front end with? I was thinking you might be able to utilize entity framework in Visual Studio. It will build the database for you. But maybe not something you want to tackle if you haven't used it before. I haven't coded in a few years, not sure what the state EF is today.

I abandoned ASP.NET MVC back in 2010 when Entity Framework was just gaining traction. It just got too expensive trying to keep up with everything changing and requiring new versions of everything every year. C#, Linq and Visual Studio we’re all amazing but since I wasn’t working in the field anymore, I didn’t think it was practical.

I’m working with Python with FastAPI framework and a Postgres database. I’m probably just going to make it work via web browser for the UI. I may add a Kivy app for remote access.

Thank you for the offer of help in making the DB. I appreciate it.

Yeah no problem. I was thinking you could use the express version of MS SQL and VS. I was envisioning this as a fat client app not a web interface. Good luck!

quote:

Originally posted by mark123:

quote:

Originally posted by Flash-LB:
I did GUIs for several hundred different projects and find them pretty easy after all that practice plus I have several years experience programming relational databases but I'll agree with others who have said that you'd be better off buying a commercial product.

Still waiting for someone to show me an example.

I'm not going to do it for you. You're on your own.

Here's a good program to do what you want.

https://www.wordperfect.com/en...rands/corel-paradox/

quote:

Originally posted by Flash-LB:

quote:

Originally posted by mark123:

quote:

Originally posted by Flash-LB:
I did GUIs for several hundred different projects and find them pretty easy after all that practice plus I have several years experience programming relational databases but I'll agree with others who have said that you'd be better off buying a commercial product.

Still waiting for someone to show me an example.

I'm not going to do it for you. You're on your own.

Here's a good program to do what you want.

https://www.wordperfect.com/en...rands/corel-paradox/

I think you gave me the wrong link.

I have absolutely no idea what you guys are talking about. None. It's all magic to me.

However, I'm curious. If Mark creates and uses his own program and has it up and running just fine, what happens if Mark kicks the bucket?

Would someone be able to get into it, so to speak, especially at tax time?

I can understand that Mark can't find what he's looking for, but wouldn't Mark, and his survivor beneficiaries, be better off using a commercially sold program, thus making things go better to extract the data, money, etc, if at all possible?

I dunno, maybe with todays modern computer whiz stuff make this a non issue?
.

quote:

Originally posted by OKCGene:
I have absolutely no idea what you guys are talking about. None. It's all magic to me.

However, I'm curious. If Mark creates and uses his own program and has it up and running just fine, what happens if Mark kicks the bucket?

Would someone be able to get into it, so to speak, especially at tax time?

I can understand that Mark can't find what he's looking for, but wouldn't Mark, and his survivor beneficiaries, be better off using a commercially sold program, thus making things go better to extract the data, money, etc, if at all possible?

I dunno, maybe with todays modern computer whiz stuff make this a non issue?
.

Note to self: don’t die.

All it is at the moment is a banking register with the customer list integrated. It’s really not as hard to code as everyone thinks.

I require the ability to tie the banking registers to the credit acceptance, mail merge, customer relations, web site, email templates and all the other good stuff that I use.

It was fine to use 6 different applications when I only had 40 customers but I’m creeping up on 200 now and it’s just taking too much time to do the books at the end of the day with multiple entries for each transaction. Let’s not even discuss mailings to 200 people. Once I get the mail merge feature running it is going to be so easy to send out my customer retention letters.

My goal is one database that eliminates the need for multiple apps and multiple entries when someone pays or when I make a purchase that requires tracking for tax purposes.

The reporting feature will be a few clicks for anyone that requires them we’re I to suddenly expire.

This message has been edited. Last edited by: mark123,

quote:

Originally posted by mark123:

quote:

Originally posted by Flash-LB:

quote:

Originally posted by mark123:

quote:

Originally posted by Flash-LB:
I did GUIs for several hundred different projects and find them pretty easy after all that practice plus I have several years experience programming relational databases but I'll agree with others who have said that you'd be better off buying a commercial product.

Still waiting for someone to show me an example.

I'm not going to do it for you. You're on your own.

Here's a good program to do what you want.

https://www.wordperfect.com/en...rands/corel-paradox/

I think you gave me the wrong link.

Nope, I gave you a link to a program called Paradox. It is one of the better low end relational database programs out there. I've programmed in it extensively and it's very flexible plus it uses proprietary C, so if you know C, you're good to go.

Mark,

I believe I have mentioned Moneydance in this forum before. MD is a double-entry bookkeeping system that is comparable to QuickBooks, but at a lower cost and much more reasonable update policy. One of the things that may not be obvious from its marketing information is that it is readily extendable through Python modules. The developers are fully behind users doing so and will assist if hiccups are encountered in the integration. It would be much less effort to build and integrate a custom client/job tracking/invoicing extension with the bookkeeping logic and register(s) already in place than to do the whole thing from scratch.

Now for some time I have used MD for my basic bookkeeping needs, and an external, non-connected application (called Totals) for invoicing, aging, etc. It has worked OK with very few multiple entries required (basically, MD tracks expenses, and the only "extra" entries required are when I enter payments received). I do not have a need for a lot of job costing or per-client P&L, I can see how this approach might be weak in that area - a possible cure might be to increase the number of expense categories in MD to cover per-client expenses.

I also use MD for my personal finances and investments, but have the business-related accounts and income/expense categories grouped under a "parent" account. This makes it dead nuts easy to extract all the Sched. C stuff at tax time.

quote:

Originally posted by architect:
Mark,

I believe I have mentioned Moneydance in this forum before. MD is a double-entry bookkeeping system that is comparable to QuickBooks, but at a lower cost and much more reasonable update policy.

Plus: It's cross-platform! Moneydance

I may look into this, myself. Thanks for the heads-up, architect

I’ve been setting some time aside for this project. I’ve probably got 8 or 9 hours in it and I’ve just about got it wrapped up. I’ve simplified everything to only include what I specifically need and got a couple more minor items in the TODO (transfers between accounts and input validation). Then I’ll work on the specific database reporting. It’s working out nicely.

Nice! Out of curiosity how many tweaks did you need to make to the db during development cycles?

quote:

Originally posted by Bytes:
Nice! Out of curiosity how many tweaks did you need to make to the db during development cycles?

Just twice but I’m going to change it again.

I think I’ve got it down but FastAPI and Python is new to me. I have a lot of cleaning up and refactoring to do. I’ve got tons of experimental stuff in my main code that I need to clear out. I’d have probably been done already if I’d have not quit programming for 11 years and didn’t have to learn new stuff while working in it.

Mark123, that is some really good planning. As time goes on you may need an index or two for performance but it sounds like you nailed it. Good work.

Have fun optimizing your code. That is the part of development that I really like.

quote:

Originally posted by Bytes:
Mark123, that is some really good planning. As time goes on you may need an index or two for performance but it sounds like you nailed it. Good work.

Have fun optimizing your code. That is the part of development that I really like.

This sure was easier on a flat database with one row per entry. I've never written such a complicated SQL in my life.

It went from this:

"""SELECT * FROM entries WHERE account_id = %s ORDER BY date; """, (id,))

to this:

"""
SELECT e.id, e.date, e.transaction_type, e.cleared, 
string_agg(t.payee, ' & ') payee, string_agg(t.memo, ' & ') memo, 
string_agg(t.category, ' & ') category, sum(t.deposit) deposit,
sum(t.payment) payment 
FROM transactions t
JOIN entries e ON t.entry_id = e.id
WHERE e.account_id = %s
GROUP BY t.entry_id, e.id, e.date, e.transaction_type, e.cleared;
""", (id,))

LOL

This message has been edited. Last edited by: mark123,

Ok, I’m redoing a lot because I just discovered htmx.

Make a post on your luck ( or results )with HTMX. My UI team is researching it right now.

quote:

Originally posted by Bytes:
Make a post on your luck ( or results )with HTMX. My UI team is researching it right now.

It’s especially nice if you hate JavaScript as much as me.

quote:

Originally posted by Bytes:
… As time goes on you may need an index or two for performance …

Any advice on which columns to index? IIRC, I should index columns I use in WHERE and JOIN clauses.

I think I’ve got everything finished other than reporting. The HTMX angle is proving to be indispensable.

Dang mark123, looks like you're getting close to sitting down, looking at your monitor, and saying to yourself "dang good job". Congrats. Sorry no advice on indexing but looking at the video posted but HTMX seems to post hand written queries to the db. There should be plenty of 3rd party tools to suggest indexing. Very similar to C# and Dapper against SQL Server (tech my team is using). You'll see a few problems fall out when you start running reports but stand back and enjoy your handiwork in the mean time

quote:

Originally posted by Bytes:
Dang mark123, looks like you're getting close to sitting down, looking at your monitor, and saying to yourself "dang good job". Congrats. Sorry no advice on indexing but looking at the video posted but HTMX seems to post hand written queries to the db. There should be plenty of 3rd party tools to suggest indexing. Very similar to C# and Dapper against SQL Server (tech my team is using). You'll see a few problems fall out when you start running reports but stand back and enjoy your handiwork in the mean time

Thanks, man.

The thing about HTMX is that you have access to all the HTTP verbs and not just GET and POST and you can apply them to any page element. You also get the ability to swap out page components without reloading the entire page. This is huge.