Topic: system date

Hi friend,
             Any body can guide how to use system date to search records. How to use date of two table for searching records of two table.
Thanks in advance.

Re: system date

Please describe your question in more details with attached project with example.

Dmitry.

Re: system date

Sir
  I have two different tables which have two different date fields. How can I search records of Two tables on basis of two dates using one button and one grid.

4 (edited by ehwagner 2020-04-14 03:29:25)

Re: system date

unforgettable,
It is sort of difficult to understand what you are trying to do without seeing a project or a picture of your requirement. However, I can tell you this much. Tablegrids can only hold data from one table. In a tablegrid you can bring in a lookup value based on a foreign key, ie DeptName from a Dept table in an employee grid, but you cannot have two tables (like master/detail table records) in a single tablegrid. There could be a workaround for what you want to do, but we need to know a little more detail of your requirement.

Re: system date

Thank you reply. I have two tables sale and expenses. Sale table date,item name and amount while expenses table has date,expensename and amount. Now I want to see cash in hand on current date or specific date.
cash in hand is equal to sum(sale.amount)-sum(expenses.amount).How it would be?

6 (edited by derek 2020-04-14 17:00:22)

Re: system date

Hi Unforgettable, Hi EHW,
Is there a reason why you need to hold your sales and expenses data in two separate tables?
I would suggest using just one table (transactions) with a transaction type to indicate whether it's a 'sale' or an 'expense'.
This way, you can display all the information in one tablegrid and it's more flexible in terms of seeing cash in hand on any particular date.
Please have a look at the attached as a possible alternative.  There is a small script but it's only doing a small amount of validation and cosmetic stuff so not strictly necessary.
Derek.

Post's attachments

Attachment icon cash in hand.zip 341.46 kb, 389 downloads since 2020-04-14 

Re: system date

Following on from the post above, if you do need to use two discrete tables, I'd suggest doing it something like the attached.
Regards,
Derek.

Post's attachments

Attachment icon cash in hand two tables.zip 338.73 kb, 349 downloads since 2020-04-14 

8 (edited by unforgettable 2020-04-16 12:51:56)

Re: system date

Hi Derek
        Thank you to upload projects. I am sending back the project by adding a form named " main". Look at the project. I tried what I want but it is not giving desired result and doubled the amount. Plz need your or any one guidness.
Thank you in advance.
Unforgettable.

Post's attachments

Attachment icon cash in hand two tables.rar 295.29 kb, 321 downloads since 2020-04-16 

9 (edited by derek 2020-04-16 17:51:21)

Re: system date

Hi,
If you're going to use SQL Query, try doing it like this (see attached).
Derek.

Post's attachments

Attachment icon cash in hand two tables 2.zip 340.25 kb, 349 downloads since 2020-04-16 

Re: system date

unforgettable,
I am trying to figure out why you want this type of summary in a one row tablegrid when you can just simply place them directly on the form. I basically took Derek's example and removed the tablegrid and placed individual fields on the form. Obviously his way works based on what you showed on the form. I'm just giving you another option to show the same summary. It works the same way, just visually a little different. If it's not suitable to your needs just throw it away.

Post's attachments

Attachment icon cash in hand two tables another option.zip 340.73 kb, 354 downloads since 2020-04-16 

Re: system date

Hi ehwagner,
                 Thank you very much to guide. I get desired result which I wanted. Can you guide me further?
Suppose I have a store which has three departments a,b and c. These departments supervised by an office. How I use your project attached here.Does it need network? How can I establish network so that office can see the activities of departments.

Re: system date

unforgettable,
I updated the project to include depts. The funny thing though is that in order to have a summary page as your first page, I had to bring back Derek's tablegrid solution to display summaries for the depts. See attached.


As far as a network, I don't know what your physical set up is. If you have several people working on their own computers, then yes you need some sort of wired or wireless network. Depending on the size of the business most businesses have a central computer called a server which houses critical data and then you have client computers accessing the data on the server. SQLite is not considered a client/server type database. You can have a few people accessing it, but you have to put controls in your MVD software to control when someone is accessing a record to see if it is already held by another user otherwise your database could get locked up or the wrong data could be saved. Dimitry put together an example on how to do that. You can search here on the forum for multiple user access. If I get a moment I will find it for you. I will say this much, you are getting into a much more advanced development. If you are new to development and new to MVD, you should master application development with MVD first, then get into multiuser environments. Depending on your situation you may need to use MySQL.

Post's attachments

Attachment icon cash in hand two tables with Depts.zip 344.06 kb, 381 downloads since 2020-04-20 

Re: system date

Hi ehwagner,
                   How are you?. Thank you for your project which provide guideline to make project which I want. I use tplink for networking. How can I manage your project. I am attaching your project adding a new form " income statement". How can I make income statement for a specific period and get print. Thank you for your guidness.

Post's attachments

Attachment icon New Folder.rar 296.25 kb, 342 downloads since 2020-04-25 

Re: system date

unforgettable,
I am somewhat confused and with me that is easy enough to do (LOL). I am not understanding your form flow and what your transactions really are. Your Income Statement form is setup like a data entry form. That probably is not your intention. First, it is not a good idea to have hardcoded expenses and income on a form unless you know for a fact that you will never need to have additional expenses or sales income in your app otherwise you will always need to be updating your app with any new expenses and sales income. It is just not good design. So you should display them as rows of data in tablegrids, which I believe Derek already has done for you. However, let me ask you this to understand your Income Statement a little better, are the expenses and sales income you stated suppose to be categories of expenses and income transactions or are they the actual transactions that get entered and then summarized by period? In other words, when you enter your sales transactions in Derek's form, do you enter in BOOKS, PENS, etc or do you enter something more detail like BLUE PENS, RED PENS, etc and then summarize by a category called PENS. Also, what happened to the depts. I thought you had multiple depts to track sales and expenses.

Re: system date

Hi ehwagner,
                  My attached project is a simple example. I want to know my income for specific period by selling various items and incurring expenses. My next step is to make balance sheet. I am trying to show data in editbox and then get a print. Data can be shown in tablegrid not in editbox .

Re: system date

unforgettable,
I had to make some assumptions in creating an Income Statement for you. Typically an Income Statement is a summary of expense and revenue categories so that is what I put together for you. I created a table of categories so that when you enter the detailed sales and expenses you assign it to a category. The Income Statement then summarizes by category and displays it on the form. There is a print button to view the report. In order to bring in the two transaction tables (sales and expenses) into the report I created a single summary table and populate it each time you run the report. I modified Derek's sales and expense forms to include a date range (period) search and to include categories. There is also a categories button to maintain the categories of income and expenses.

Post's attachments

Attachment icon Income Expenses.zip 352.12 kb, 354 downloads since 2020-04-29 

Re: system date

Hi ehwagner
                Thank you for project. That guide me much more. I am making a project following your project. I am facing a problem. For printing you use print button click procedure. On script, last few lines of print button click procedure where you created variable for Form1,I could not  understand these lines because these lines creating problem by red highlight. You created two tg sales and expenses on a form but I created them on two different forms.

18

Re: system date

Unforgettable,

Have you changed the form names for the two table grids?

For example if your form is named frmSales    you would need to change FORM1.tgSales.  to frmSales.tgSales.

When you get the red error line what error message is at the bottom of the IDE ?

On a clear disk you can seek forever

Re: system date

Hi ehwagner,
               Yes two separate form named expenses and sales. I got error message undeclared identifier: form1 15.10
Secondly your project is giving wrong net income printout 199.03 while on form it is 58 which is correct.
Thank you for quick response.
Have a nice day.

Re: system date

unforgettable,
The solution I provided is based on the project posted on the forum. It sounds like what is happening is that your real project is setup differently and my solution is not going to work. It would be better if you just posted your real project so we can see how your design is defined and can properly help you. You can remove any real data from the database.

21 (edited by unforgettable 2020-06-18 07:25:04)

Re: system date

Hi ehwagner,
                       I am attaching your Income Expenses project with some modification but it does not work properly. on form1, I added more Edits and comboboxes. Same with form2. But it save only one record of form but not other. Can you help me to make project  proper which show other records of form.
Thanks in advance.

Post's attachments

Attachment icon Income Expenses.zip 338.78 kb, 292 downloads since 2020-06-18 

Re: system date

unforgettable,
A form can only update one record selected from a tablegrid at a time. By having the same fields defined on the form will only show the same data multiple times. I'm not sure what you are trying to do. Maybe you can explain a little more what you want to accomplish.

Re: system date

Thank you ehwagner. It means we can not enter second record on table using Edit same time. I have to create another table for second record but how can I make bill to print using Thermal printer.
For example I have store. A customer comes and purchases one item, another comes and four items. How can I enter this sale and make a bill to print using thermal printer for customer?

Re: system date

unforgettable,
In order to group multiple sales items into a single sale I created another table called Sales_Tickets. Each ticket can have one or more items sold. I included a print for each ticket. However, I know nothing about thermal printers, but I would assume that the report can be printed directly onto a thermal printer if it is attached to the computer/network you are using for your project.


As a footnote, since the sales items include a date, I put in script to keep the sales items date in synch with the sales ticket date. The sales items date probably should be removed and only use the sales ticket date. I did not remove it because I didn't want to interfere with the already defined income / expense forms and summaries. Not enough time on my part to redesign it. Hope this helps.

Post's attachments

Attachment icon Income Expenses Revised.zip 354.07 kb, 368 downloads since 2020-06-19 

Re: system date

Thank you ehwagner you have solved my big problem. Can I ask something?
First we can create more than two field on category table? Suppose the attached project has two category just income and expenses. We can add 'service' field?

second how can we add Grand Total, discount, receivable and balance.
Thank you so much. God bless you.