Complex selection queries on multiple tables

[nicoloose]

This is a difficult question to post but I will do my best to make sense.

I have a manufacturing company and we make produce to order only. So I have created an application with a customer table with customerID and customerName.
I create a sales record in the sales table which links to:
1. customer table by customerID
2. products table by productID
I also have a salesDate and a salesQty and salesID. The sales table links to the production and collections tables using a salesID, productID and customerID so that I can track sales, production and collections BY customer BY product for each month.

I need to create a monthly stock accounting report that will show all sales, production and collections to date and all sales, production and collections for the current period. From this, I can work out what stock I should have on hand and what outstanding collections there are per customer by product.

I really need help putting together selection queries and manipulating the resulting array to show all this information. I would post what I have tried but I end up having too many lookups and my code becomes messy and difficult to understand. I have attached my sqlite database with data.I have also attached a spreadsheet of how my current stock accounting report looks.

Any help here would save me a lot of time and frustration.

[icouto]

A couple of generic hints, which may or may not be useful:

1) You will need to sort, order and summarise your data. There are 2 places where the sorting, ordering and grouping can be done: either at the database level, or inside LiveCode, once you get your raw data into it. Databases are written for handling and aggregating large amounts of data, so it is usually a good idea to let the database handle the collection and aggregation of your data, and then present it to your user using a nice interface you designed with LiveCode.

LiveCode has commands that help you connect and send commands to various databases, but in order to get the database to do things for you, you will still have to speak its language: SQL. One of the KickStarter campaign goals was to write a new database library, which hopefully will abstract away the need for us to use SQL in the future, but that seems to be still a *very* long time away - so you have to bite the bullet and learn some SQL.

The good news is that SQL is not a complex language. If you are not already comfortable with basic SQL, it may be a good idea for you to have a look one of the many good tutorials around, such as this:

http://www.w3schools.com/sql/

You should pay particular attention to the sections about SQL FUNCTIONS - such as "GROUP BY", "COUNT()" and "SUM()".

If the database you will be using in your final product is SQLite, then you should be aware that SQLite has excellent documentation in their website for all the SQL features they support. For instance, the documentation for the "aggregate functions" supported by SQLite is here:

http://www.sqlite.org/lang_aggfunc.html

The SQLite docs use diagrams to explain how their engine tokenises your SQL. These diagrams often make it easier for you to understand how to build complex queries using syntax that will be understood by the parser - have a look here:

http://www.sqlite.org/lang.html

And the syntax for a SELECT statement is here:

http://www.sqlite.org/lang_select.html

2) The system you are trying to assemble is complex, and I am not sure I understand exactly what the help is that you need. It sounds to me as if this is more a 'generic' cry for help, rather than a request for assistance with a specific problem. It may be easier for us to help you if you break down your tasks into manageable pieces, and then ask for help for more specific questions and difficulties you may have along the way.

You state that you have already written some code, but that you think it has 'too many lookups', and that it looks messy. Using proper SQL queries and the right LiveCode messages we may be able to minimise the number of lookups. But regardless of how 'messy' your code might be, it is still a starting point, and better than nothing! If you show us what you've done, we may be able to help you improve on it.

[nicoloose]

I did not get the chance to thank you for this in depth answer to my question. I have battled long and hard and have actually managed to write a complete system which works well in my business using Livecode. Now that it is finished though, I see that there are many places for improvement, both in the execution of the code and also the way in which the system flows.
Trial and error is a dangerous method to develop systems that will run your business but it's the greatest way to learn!!

Thanks again!