Changing a field

[Marie_(Guest)]

I am working on an aging report. The field we use for terms is a Character field because the terms could be anything from due on 10/15/2009, to 10 Net 90, to 45/90/180. So we can^t use a number field or a date field. __However, once I get to the report I need to change this into a date, based on the invoice date and then I need to use this information to determain the amount of the invoice due. __This is for internal aging so we can work with our cash flow. __I don^t seem to be able to work any expression to get me what I need - any help would be GREAT! Even if I have to send this out to Excel to make it work that would be fine.

[kfleming]

Will all dates by show as due on followed by the date as a text string? How many other different options could be in this field?____There is probably as easy expression we can build to get the date for you once we know the rules of what can be in the field.____Kathleen__R&R Support

[Marie_(Guest)]

No, it will not say "due on" it would just have the "10/15/2009" the other special terms we use at this time are as follow:____"3.8%" - of Balance Paid weekly - this is__ terms per a signed agreement____"30 / 60 / 90 / 120" - 1/4 of the invoice due every month____"3­F 10/Net 11" - 3% discount if payed in 10 days Due within__ 11 days The ADF is "After Deducting freight"____I would like to be able to change any of these terms into a due date and then another field with the amount due on that date.

[Marie_(Guest)]

Did I answer all of your question?

[kfleming]

Start with:__IIF(ERROR(CTOD(TERMS)),date(),CTOD(TERMS))____This will give you the date from the terms field if that field contains only a date string otherwise it will give you today^s date.____To handle the other possible values, I would need to know more about what date you want to return if for example the field contains "3.8%". Do you want that date to be a week from today^s date?____We can make the IIF more complex to add additional conditions but again it depends on what you want to return based on the value of the terms field.____Kathleen__R&R Support

[Marie_(Guest)]

What I would like to get is something that looks like this:__CustNo....InvNo..InvDate...Total$Inv....Terms....DueDate...$Due____Cust1....123....04/14/09...40985.47...60/90/180...6/15/09...13661.83__..................DueDate&Due$....................7/15/09...13661.83__..................DueDate&Due$....................10/15/09...13661.81__Cust2...456...04/14/09...75093.93...10/15/2009...10/15/09...75093.93__Cust3...789...04/16/09...95753.99...3_/net11....4/26/09...93911.37__Cust4...321...03/16/09...80450.23...PrAgrment....03/22/09....3218.01__..................DueDate&Due$...................3/29/09....2316.97__Cust4...654...04/03/09...80550.50...PrAgrment.....4/04/09....6218.63__..................DueDate&Due$....................4/09/09....5989.69____Maybe take Customer 4 out to 10 weekly Payments and Customer 3 would show due in 10 days with the 3% discount on merchandise and $1,000 in freight on the total invoice.__I will need to do this for accounts recievable as well as accounts payable and then mesh the two reports together. And that I would probably want to send to a pivot table with the Invoice No, Invoice total, due date and then the Due amount as the body.__I just added the periods for spacing. ____Let me know if you need more information.____Thank You so much for the help Kathleen! __

[gjshuger]

I don^t mean to butt in, but it seems to me that, while R&R might be made to handle this situation (perhaps with a UDF that encapsulates the various terms), it might be done more efficiently with some sort of pre-processing code in your accounting program. Is this even possible? And regardless of where the logic is performed, you^ll need to make sure that the terms (e.g., "60/90/180", "3_/net11", etc.) follow some established formatting rules in order for them to be parsed consistently.

[Marie_(Guest)]

Our Accounting program has a table for such terms and will hold the information; however it will not give back the due date and due dollars and it will only return a 2 character code for the terms not the actual terms themselves. __The terms will have to go into a table that has a drop down menu for selection at Order Entry time. What formatting rules should I use when setting up terms in this table? It is up to me to get this correct so what I do to make things work is pretty open.__I guess this could be written from either side, the UDF from Order Entry side or the table from the Accounting side.

[gjshuger]

If you use the 2-char. code to determine which terms formula to use, you can ignore the text string (e.g., "1_;NET11") and just build the factors into the program logic. Otherwise, you just need to express the terms in a way that can be used by the program that will be calculating the dates and amounts. For example, a string like "45/90/180" could be broken apart and converted to give the 3 numbers to add to the invoice date to come up with the due dates.____The biggest issue I see is that some of the terms will generate multiple output lines, perhaps an unknown number in some cases. I don^t know if that can be done with R&R. That^s why I suggested that some sort of preprocessing might be needed to generate a temporary file to be used as the input to R&R.

[Marie_(Guest)]

We can certainly use the terms codes from the accounting software especially if it will make the report easier to write. Can we - or - would it be helpful to limit the multiple lines to 8 or 10 lines?__The temporary file for the preprocessing would need to be crated in the accounting program, no way to do that in report writer - say create a report and then the second report from the 1st report?