Validate Data Format
-
I'm working on a WPF app for a client that gets invoice remittance data from their customers as CSV files. My app extracts the Invoice Date, Reference Number, and Amount using metadata defined by my client for the input files. This metadata defines the columns in the file, and the index position of the Invoice Date, Reference Number, and Amount. The key is the reference number. It is used to look up the invoice row in their accounting system. Once in a while their customer formats the reference number wrong. For example, one of the reference number formats is eight numbers, as in '20158011'. But from time to time, they send invalid data, as in 'RECOUP19718470_002ZT'. Another customer's format is 'RDHC608965'. This column's reference number always starts with 'RDHC'. The remainder is the six-digit reference number. What I would like to do is provide a place in the UI to define the required format of the reference number. If it was internal I would specify a regex, but this is something that my client has to define. What's the right way to go about this?
In theory, theory and practice are the same. But in practice, they never are.” If it's not broken, fix it until it is. Everything makes sense in someone's mind.
-
I'm working on a WPF app for a client that gets invoice remittance data from their customers as CSV files. My app extracts the Invoice Date, Reference Number, and Amount using metadata defined by my client for the input files. This metadata defines the columns in the file, and the index position of the Invoice Date, Reference Number, and Amount. The key is the reference number. It is used to look up the invoice row in their accounting system. Once in a while their customer formats the reference number wrong. For example, one of the reference number formats is eight numbers, as in '20158011'. But from time to time, they send invalid data, as in 'RECOUP19718470_002ZT'. Another customer's format is 'RDHC608965'. This column's reference number always starts with 'RDHC'. The remainder is the six-digit reference number. What I would like to do is provide a place in the UI to define the required format of the reference number. If it was internal I would specify a regex, but this is something that my client has to define. What's the right way to go about this?
In theory, theory and practice are the same. But in practice, they never are.” If it's not broken, fix it until it is. Everything makes sense in someone's mind.
"Reference data" is just that; there's nothing to validate. It's like a "comment"; subject to the whims of the client and not something to build a "system" around. "Invoice payments" are generally applied to outstanding balances, and not any particular invoice; that shows how "important" the reference number is (not).
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
I'm working on a WPF app for a client that gets invoice remittance data from their customers as CSV files. My app extracts the Invoice Date, Reference Number, and Amount using metadata defined by my client for the input files. This metadata defines the columns in the file, and the index position of the Invoice Date, Reference Number, and Amount. The key is the reference number. It is used to look up the invoice row in their accounting system. Once in a while their customer formats the reference number wrong. For example, one of the reference number formats is eight numbers, as in '20158011'. But from time to time, they send invalid data, as in 'RECOUP19718470_002ZT'. Another customer's format is 'RDHC608965'. This column's reference number always starts with 'RDHC'. The remainder is the six-digit reference number. What I would like to do is provide a place in the UI to define the required format of the reference number. If it was internal I would specify a regex, but this is something that my client has to define. What's the right way to go about this?
In theory, theory and practice are the same. But in practice, they never are.” If it's not broken, fix it until it is. Everything makes sense in someone's mind.
Kevin Marois wrote:
What's the right way to go about this?
Your requirements description is incomplete for a start. You must also answer the question - what do they want to happen when it is wrong? For example discard the entire csv, ignore than one row, collect the failures and present immediately to a human, collect somewhere and allow for a report. Might be some others. You should also decide who actually manages it? Is it set up on install? Can it change day to day? Can different users change it (so not the customer but individual customer users.) Those drive how you configure it. For example if install then you would need to ask that during install. (This probably is not viable since their needs might change over time.) But other than that your application should have a section specifically for customer configurations. Yes plural. Presume one now and more for the future. And possible one for the application (admin users only) and one for normal users. You application might not support multiple users so the different levels might not apply. With both application level and user level then you need to decide if the user one overrides the application one completely or if both work. You can save the configuration in a configuration file or a database. Or other persistent store. You would normally load the configuration on start up. Naturally updates while running must impact the loaded configuration also.
-
"Reference data" is just that; there's nothing to validate. It's like a "comment"; subject to the whims of the client and not something to build a "system" around. "Invoice payments" are generally applied to outstanding balances, and not any particular invoice; that shows how "important" the reference number is (not).
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
Gerry Schmitz wrote:
"Reference data" is just that; there's nothing to validate. It's like a "comment"; subject to the whims of the client and not something to build a "system" around.
It's not 'Reference data'. It's a Reference Number. The Reference number in the customer's data file is a 'reference' to an Id in the client's accounting system. This matches an invoice from the client to a customer's payment. It's a reconciliation. But that's not the question. See this[^] Since my client can define new customers in the app, I need a way for them to tell me the FORMAT of the Reference Number. Then, my app is reading the file, I can verify the number so it can be looked up. The format of the Reference Number CAN be validated. I COULD use a RegEx, but defining the validation for it has to be user friendly. The question, again, is how to allow the user some way of manually inputting the format of the reference number.
In theory, theory and practice are the same. But in practice, they never are.” If it's not broken, fix it until it is. Everything makes sense in someone's mind.
-
Gerry Schmitz wrote:
"Reference data" is just that; there's nothing to validate. It's like a "comment"; subject to the whims of the client and not something to build a "system" around.
It's not 'Reference data'. It's a Reference Number. The Reference number in the customer's data file is a 'reference' to an Id in the client's accounting system. This matches an invoice from the client to a customer's payment. It's a reconciliation. But that's not the question. See this[^] Since my client can define new customers in the app, I need a way for them to tell me the FORMAT of the Reference Number. Then, my app is reading the file, I can verify the number so it can be looked up. The format of the Reference Number CAN be validated. I COULD use a RegEx, but defining the validation for it has to be user friendly. The question, again, is how to allow the user some way of manually inputting the format of the reference number.
In theory, theory and practice are the same. But in practice, they never are.” If it's not broken, fix it until it is. Everything makes sense in someone's mind.
It's reference "junk" when it's in some one else's system that you have no control over. And anything you put in the way of "the payment process" makes you the problem. A company issues their own "client numbers", Purchase Order numbers, Invoice Numbers, Product Numbers, etc. which becomes "reference junk" in someone else's system that they then send back to you as more "reference junk". You reconcile your own paperwork; not someone else's. I'd send "checklists" of the outstanding invoices if they wanted to "reference" something.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
It's reference "junk" when it's in some one else's system that you have no control over. And anything you put in the way of "the payment process" makes you the problem. A company issues their own "client numbers", Purchase Order numbers, Invoice Numbers, Product Numbers, etc. which becomes "reference junk" in someone else's system that they then send back to you as more "reference junk". You reconcile your own paperwork; not someone else's. I'd send "checklists" of the outstanding invoices if they wanted to "reference" something.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
This whole process is outside of this discussion. I have no control over how my client processes their invoices. Thank anyhow
In theory, theory and practice are the same. But in practice, they never are.” If it's not broken, fix it until it is. Everything makes sense in someone's mind.
-
This whole process is outside of this discussion. I have no control over how my client processes their invoices. Thank anyhow
In theory, theory and practice are the same. But in practice, they never are.” If it's not broken, fix it until it is. Everything makes sense in someone's mind.
You have (some) control when inputing from documents; then you can use "fuzzy" searching then and there. You said CSV ... which means, you have to get it into the system "before" you can do anything with it. Throughput.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
Kevin Marois wrote:
What's the right way to go about this?
Your requirements description is incomplete for a start. You must also answer the question - what do they want to happen when it is wrong? For example discard the entire csv, ignore than one row, collect the failures and present immediately to a human, collect somewhere and allow for a report. Might be some others. You should also decide who actually manages it? Is it set up on install? Can it change day to day? Can different users change it (so not the customer but individual customer users.) Those drive how you configure it. For example if install then you would need to ask that during install. (This probably is not viable since their needs might change over time.) But other than that your application should have a section specifically for customer configurations. Yes plural. Presume one now and more for the future. And possible one for the application (admin users only) and one for normal users. You application might not support multiple users so the different levels might not apply. With both application level and user level then you need to decide if the user one overrides the application one completely or if both work. You can save the configuration in a configuration file or a database. Or other persistent store. You would normally load the configuration on start up. Naturally updates while running must impact the loaded configuration also.
I agree 100%, have some or other way to capture this to be used in the future, irrespective of which customer adds what as their own reference. Just do a callback then to that specific customer and you should be able to tie up the reference to the correct file reference.