Taking a look at at least 2 consecutive payments in the bank file would be helpful imho.
Matching the payment with the invoice should be done on more criteria, anyway, one of which is of course the invoice amount.
I need to be able to extract the client code which is unique and deposits would be made once or twice a year. The amount are very similar example $163.00 1,550 plus number.
Once I can extract the code then I can match the payments, except if the case of an under payment, which will be handled.
Thank for your response.
If the file is CSV then the code will be in a consistent position, even if left blank. For every line, it will be between comma n and comma n+1. By replacing commas with returns you will transform the line into a list, then you can get the code with GetValue().
My thoughts as well, si! however OP says it may be that the single 'Description' column is where this code appears with other text and is client-defined, thus inconsistent.
I'd say it's time to have the bank provide a unique column, or have a human (possible errors!) go through this field and find the value.
This assumes that you are used FM7 or newer.
That all letters of the alphabet are used regardless of their case.
That the left 3 characters are only alpha and the right 3 characters are numeric.
As long as the Client ID is separated by characters the “word” functions recognize as the end and start of a word it is not hard to pick it out. However in the attached script I show two ways to test for your Client ID one is word by word and the other steps though character by character and there are only two lines that need to be changed to switch between them. The character lines are the Disabled “//” ones in the script. Also the character by character will find the Client Id info even if the client leaves out a space which will cause the word by word to fail.
Of course you could run the word by word search first and if it fails to find it then run the character by character search. The character search takes longer but won’t miss if it’s there but, both will fail if there happen to be the same string pattern before the real ID.
Then there is the possibly that only certain letters are used in one of the 3 positions, i.e. The first character may be only an A, B, G, W or Z. Giving you a filter string of $$AlphaStringLeft = “ABGWZ” add lowercase to it if needed. Then you need to change the filtering test.
(Length ( Filter ( Left ($TestString ; 1 ) ; $$AlphaStringLeft )) /* test first 1 character is alpha and in “ABGWZ” */
+ Length ( Filter ( Middle ($TestString ; 2 ; 2 ) ; $$AlphaString )) /* test second and third characters are alpha */
Length ( Filter ( Right ($TestString ; 3 ) ; $$NumericString ))) /* test last 3 chars are numeric */
etc. or some variation.
/* All the Letters, but if any letters are never used in the Client ID remove them from this string*/
$$NumericString = "0123456789"
Find Client ID.pdf 26.6 K
I don't understand how and why a bank can supply such a disaster, to start with.
In Switzerland all banks supply a .v11 file (which is a simple text file) and everybody knows how to decode it.
Change bank !