Hey Lubos,
Are custom fields supported in Custom reports now? I still have monthly requirements of pulling order information based on state (custom field in Customers) the customers are living in. This is required for filing GST in India.
Please do not divert topics with unrelated questions. Your question had nothing to do with errors in SELECT statements. I have moved it to a new topic.
To answer your question, you do not need a custom report to obtain the information you want.
Create a custom field for sales invoices and show it as a column. In this example, I made the custom field a dropdown list to ensure standard entry of state names:
Sort the Sales Invoices tab by clicking on the heading for the Invoice date column. This will place all the sales invoices for a time period together, regardless of whether they have been paid or how long overdue they are:
Click Export but when copying data, choose only those invoices through the desired month. Note that you will want to copy the heading row, so you may also be grabbing some more recent invoices (although not in this example):
Paste into a spreadsheet and delete any rows that are out of the desired date range:
This takes much less time to do than to read about. The monthly spreadsheet is 2 clicks, 1 Copy and a Paste.
1 Like
Thanks for moving the thread to a relevant one. Although this sounds like a workaround, my custom field ‘state’ is in Customers category and it’s been there for more than a year. This state shows up on individual invoices just fine. Making a new custom field state in invoices will not help me here.
Do you have a solution for this kind of situation where State is in customer’s table? Since it seems customer addition is not related with a date he was added on, looks like I’m stuck in such a case.
Yes, but it won’t do you any good, because the Customers tab doesn’t contain the date-related sales invoice information you are seeking. It only contains total number of invoices and current Accounts receivable balance.
I don’t know why you call it a workaround. The program will display the information you want with a single click. A couple more simple actions put it into a format for your unique (among all countries of the world) new tax filing requirements. You can have the result faster than you could type in the query for a custom report.
The requirement you are talking about is Place Of Supply which is necessary for filing tax returns.
When filing a tax return, it is the transactions you have to show rather than your customers and suppliers.
The data should be relevant to your sales and purchase invoices.
So rather than setting a state code in the customers tab, create a custom field for Sales Invoices like @Tut has shown above.
At present there is no documentation for Custom reports. Even when this is properly implemented, i doubt you will be able to pull data from Sales Invoices and Customers tab simultaneously. So its better to have the data where they actually belong.
@Tut So, looks like I’m painfully stuck here because of past decision of putting ‘State’ in customers’ tab (my bad). I thought all customer related information like her address etc. is to be in Customers tab. Didn’t know I’d have to face this kind of problem some day.
Your solution was just fine and I hope it’ll help other Indian users.
@sharpdrivetek Yes you are right, this is for Place of Supply and my accountant is asking for this information to file GST. I’m a programmer so I know how to pull data using SQL, however, Joins are not supported with Custom reports and yes documentation isn’t available much.
I think report section should have few more reports like this as most of the people will be requiring such reports.
You can edit those past sales invoices after the custom field has been created. The new tax scheme has not been in place for very long. You would only have to edit sales invoices issued since the start of the scheme. The difficulty is that address of the customer is not shown on the edit screen. So you will need to View first, take note of the state, then edit.
I’ll try that. Thanks for the help.