x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 11 replies
  • Subscribers 119 subscribers
  • Views 3992 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
963
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
mhainesalt

Script to conditionally modify discount date during visual integrator job

Posted By

Good afternoon.  

I have a client that wishes to import invoices from a data collection system into Sage 100 ERP 2016.  We have a working Visual Integrator job for that purpose.  The client has a unique requirement however that they wish to populate the discount date for all invoice records even when there is no discount offered.  For example a net 30 invoice would have the same discount date as due date.  The business logic will only populate the discount date if the discount amount on the terms code has a discount amount other than zero.

I would like to evaluate the discount date during the visual integrator import and determine whether a value was written to the discount date and if not write the due date to that field.  I have not been able to come up with any run after scripts or conditional criteria to perform this with a single visual integrator job.

Does anyone have any recommendations or suggestions?

Thanks in advance.

  • 0
    What I would do is insert the Discount Date into the VI import job and give it the same date as the due date on the condition the terms are net 30. This should bypass the business object. Give it a try.
  • 0 in reply to BigLouie
    Thanks for this idea BigLouie! I should mention that there are multiple terms codes in use that do not offer discounts, so I was looking for a way to evaluate circumstances where the there was not a discount in the terms, so that the VI job did not need to be maintained every time they created a new set of vendor terms that did not include discounts. If not this will be an achievable plan B.
  • 0 in reply to mhainesalt
    You should be able to use a temporary field with a file assign from the terms code file to get the discount rate.

    Then use a conditional using the amount in the temporary field on the due date field based on that to suppress the assignment to the due date.
  • 0 in reply to TomTarget
    Tom. That sounds right on target. I will try that. Thanks very much!
  • 0 in reply to mhainesalt
    It looks like the temporary field for the discount rate worked, but it has to be a calculation and not a file assign because that is a numeric field. The place where I am stuck now though is that the due date is not in the import file, and calculating the due date or discount date does not appear to be working. I thought there was some crazy syntax we had to do to do date math in pvx, converting the date to julian and adding the days before due and then converting that value back to a date. Does anyone know the syntax for that? Thanks
  • 0 in reply to mhainesalt
    What version are your running? If I recall, the more current versions should automatically calculate dates and rates automatically if you let it. Essentially, by bringing in the terms code, the system will auto populate due dates, discount dates, discounts, etc. In other words, don't try to assign them yourself, just let the system do it.

    Older legacy modules require specific assignment/calculation of every field. I often get trapped doing imports for payroll because I forget this. Recently had one where I forgot to assign the earnings type (Regular, Overtime, Vacation, etc.) during an import and it made deductions based on certain earnings types calculate incorrectly.
  • 0 in reply to TomTarget
    Tom thanks, this is 2015 sr 2. The thing about this is that we are not doing something that the business logic ordinarily does in this instance. We are populating the discount date with the due date when there is no discount. This enables cash management to look in one place for bill payment rather than both columns. The due date is automatically calculated fine, but does not seem to be available for use in the import because it has not been written yet while the job is running. The conditional logic is working fine as well.
  • 0 in reply to mhainesalt
    Do you have a copy of the Providex Language Reference? I've got a copy I picked up long ago that lists all of the functions available (including ones that are not listed in the visual integrator functions box). It has been an invaluable tool to me in trying to do more complex functions.

    There is a Julian date function listed in it that might help you out.

    Private message me and I we can set up a way for you to get a copy of it.
  • 0 in reply to TomTarget

    Actually, here is a link to a more up to date version.

    docs.pvxplus.com/PVXV7.pdf

    Look under System Functions in the table of contents.

  • 0 in reply to TomTarget
    Thanks. I used the following function which appears to be syntactically correct, but it returns an error when the job is run DTE(JUL({AP_InvoiceHeader.InvoiceDate$})+{AP_TermsCode.DaysBeforeDue})

*Community Hub is the new name for Sage City