Daily Sales Invoice report - Additional filtering requirement

[bkm888]

When the original requirements for this report were relayed to MNTechnique, we mistakenly left out an important filter for the report generation.

The external accounting system handles unidentified customer sales (generic walk-in customers) that take place inside their stores differently than the rest of the company sales. The sales to unknown customers are to be EXCLUDED from this report. We will have to contract the developers to generate a very different kind of report for those sales as another project.

In order to filter them out of this report... Any Sales Invoice where the CUSTOMER ID is either CWALKIN or RWALKIN should not be added to this report.

It is not possible to narrow everything down to a single generic customer because all of our taxing is done by the county field in the customer record. The stores are in different counties. This is why we went with the separate generic walk-in customers for each store location so the taxes would be properly calculated.

Ideally it would be good if there was a way to add customer ID's to those 2 later. When the next store opens the generic walk-in customer could then be added to the exclude list. If this is not possible, then we will just have to have the source code for the report altered if we add or changes stores.

We are open to your suggestions on this.

[ghost]

@bkm888 Here is how we can handle this requirement:

• We checked the customer groups that you have been using are (All Customer Groups, Commercial)
• We can add a 'Customer Group' as 'Stationary Customers' (or any name that is more suitable)

-In this Customer Group we can add these walk-in customers that you want to exclude from the report.

• In Daily Sales Invoice report, we have added a new filter for 'Customer Group' that will help us differentiate the invoices.

-Also this way, you can avail the report of all the customers too if you clear the filter.

[bkm888]

That sounds like a good plan. When would we be able to test this?

BKM

[ghost]

We will have the test server updated today, for you to test.

[ghost]

@bkm888 When initially requirements were shared with us, Daily Sales Invoice Report did not have 'Customer Group' column. We have added this here for the better understanding of this particular approach. So checking if you would like to keep this column in the report?

[bkm888]

Thank you. We will run testing over the course of the weekend and report back.

Once this is finished the new filter testing this weekend, we have one more round of customization we would like your team to do for this report. We have determined a list of several columns we would like to remove from the report. We have determined they will never be used and they only add to confusion. We also determined another date column that needs to be populated that had previously been blank. We will need your team to let us know how much additional work that will be. However we want to test the additional filtering first.

Thank you,

BKM

[ghost]

@bkm888 Okay. We will have these changes up, on the gcp test server for UAT.

For the other additional customizations, we can work on the GitHub issues like these. Thank you.

[bkm888]

I am not familiar with "UAT" What does that mean?

[unmesh87]

Needs end user acceptance to push changes on production environment.

[bkm888]

OK Thank you. Noted that in my notebook.

[ghost]

@bkm888 We have updated the gcp test server with the latest changes.

[bkm888]

The updated gcp test server does not appear to work. I have changed the 2 customers I am interested in filtering out to be part of the "Anonymous" group. All others are "Commercial" group. I used the Data Import function to make sure all records were either Anonymous or Commercial and imported the update to the Customer records. I ran the report example as below. Example:

• Select the report
• Select March 29, 2018 as both the start and end dates
• Select Customer Group "Commercial"
• Delete the Owner so all records are evaluated

The resulting list has the "Anonymous" records included with the others. Have I missed a detail in the setup, or is there another problem?

BKM

[ghost]

@bkm888 In your system, There were two customer groups used

• Commercial (for all customers)
• All Customer Groups (for CLT walkin and RDU walkin)

When you changed the customer group from All Customer Groups to 'Anonymous' for those walk-in customers, The report did not reflect the changes because of the back-dated entries of the same customer with old customer group.

Now on, when we will create a new sales invoice for walk-in customers, It will have these customers as grouped 'Anonymous' as required. Like so:

Also, Having all other required customers in 'Commercial' group gives the desired outcome in the report. Example:

• Select the report
• Select Customer Group "Commercial"
• Report is evaluated for all customers EXCEPT walk-in 'Anonymous' customers.

[bkm888]

I was not aware that the records recorded to "Customer Group" in the sales invoice at the time of creation.   Is this what caused the changes to NOT be reflected in the report output?   This presents a bit of a problem. The sales users create new customers all the time and they frequently use the default "All Customer Groups" in their haste to get the sale completed. We usually have to go behind them and fix these errors later, but in this case, we would never be able to get those sales tallied with the others.   Is it possible to use the current (most recent) group designations of customers instead of a group designation embedded in the sales invoice?   The fact that you were able to find a list of "All Customer Groups" customers is indicative of the problem with this reporting model. After this report was created and you explained how it worked, I used the bulk edit ability of the "Import Data" tool to align all of the accounts in order to get the desired results.   This means that I do not have any data to actually test the report functions now.   I am looking for suggestions about how to continue with testing this feature.   What if.. What if I made the groups name changes in my live production server and let it run for a few days, then take a backup of it and restore it to the GCP test server? Would that work? It would certainly give me plenty of real data for testing.   Thank you,   Bryan Miles JMI Barcodes bkm@jmibarcodes.com       -----Original Message----- From: "Siddhi Sawant" [notifications@github.com] Date: 05/14/2018 06:19 AM To: "mntechnique/jmi_erpnext" jmi_erpnext@noreply.github.com CC: "Mention" mention@noreply.github.com Subject: Re: [mntechnique/jmi_erpnext] Daily Sales Invoice report - Additional filtering requirement (#43)

@bkm888 In your system, There were two customer groups usedCommercial (for all customers)All Customer Groups (for CLT walkin and RDU walkin)When you changed the customer group from All Customer Groups to 'Anonymous' for those walk-in customers, The report did not reflect the changes because of the back-dated entries of the same customer with old customer group. Now on, when we will create a new sales invoice for walk-in customers, It will have these customers as grouped 'Anonymous' as required. Like so:

Also, Having all other required customers in 'Commercial' group gives the desired outcome in the report. Example:Select the reportSelect Customer Group "Commercial"Report is evaluated for all customers EXCEPT walk-in 'Anonymous' customers.— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.   

[ghost]

@bkm888 Yes. By using your current grouping system for customers you can achieve the requirement.

• Commercial group has all the customers except, walk-in customers
• All Customer Groups has all walking customers (RWalkin/ CWalkin)

This grouping will provide you the workaround solution. All your walk-in customers can be grouped as 'All Customer Groups' and rest identified customers as 'Commercial'.

Steps to replicate the scenario:

1. Select the report
2. Choose filter as 'Commercial' >> Shows the list of all customers EXCEPT RWalkin & CWalkin 'All Customer Groups' >> RWalkin/CWalkin Customers

This will work for all the old invoices also.

[unmesh87]

@bkm888

Checking if you had a chance to run through above suggestion.

[bkm888]

I have been reading and re-reading the last reply. I have been unsure how to proceed with the testing. Now that I have altered all of the records in the database to be either Commercial or Anonymous, I will not be able to tell which ones were previously "All Customer Groups."

I know the two accounts I was most concerned about will likely be in the "All Customer Groups" list but there were also several others that didn't belong in that group so I fixed them during the reordering of the groups.

So I guess I am wondering if I have polluted my data? Or if it is still possible to get something meaningful in the test?

I am sure there is some logic here that I am just missing, but it has me stalled.

BKM

[unmesh87]

@bkm888

Hope the call was productive to clarify how the filter works for walkin and commercial customers.