Skip to Content
author's profile photo Former Member
Former Member

Print Layout Designer - Field: External Data Help

First and foremost, I've gone through all the e-learning tutorials on Print Layout Designer and they did not cover the External Data Field object, thus, I'm in need of some help.

Currently, I'm modifying the base A/R invoice in Business One. I've gotten pretty far but my supervisor would like me to add the original Sales Order number. Currently as most of you know, I do not currently have access to since it is up two base document levels from the invoice.

With that said, I was wondering if I could use the External Data Field to help pull in the Sales Order number. If so, how do I use this field? Under content tab it is asking for a procedure name; is this a stored procedure? If so, can you pass parameters to it with the procedure name?

Any help/guidance would be greatly appreciated,

Nate Hanna

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 22, 2007 at 11:12 AM

    The Base document reference is stored at the row level.

    ie; its in INV1 table.

    To get the Sale order no, create a database type field in the Print layout designer and select in the content tab

    "AR Invoice Table rows "- as table

    and Basedocument no as coloumn.

    You can use the The Externaldata field for printing fixed images etc.. and the procedure name is the name of the .dll file

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member


      Thank you for your help but from what I understand the base document of a A/R invoice is the delivery document for the warehouse not the original sales order. Looking at the INV1 table confirms this understanding for the column <b>Base Document Reference</b> matches the unique identifier of the Delivery Note in SAP BO.

      Now, to take that a step further the base document of the Delivery note is the original sales order; so is there any way to retrieve the Sales Order No. that is two base documents up in SAP BO?

      Let me know if I'm still not being clear,

      Nate Hanna

  • author's profile photo Former Member
    Former Member
    Posted on Jan 22, 2007 at 07:56 PM

    Could you run a sql statement to retrieve the document number from the RDR1 table?

    For Example

    select docnum from ordr where docentry = (select baseRef from dln1 where docentry = (select baseref from inv1 where docentry = '1'))

    where '1' is the docEntry for the current invoice you are working with. If you have different base types you could make it smarter by selecting the correct table based on the base type.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hello Everyone,

      I thought I would post my solution for others that may have the same issue. After thinking about my problem and looking at the SAP interface for a day or two I saw the solution in front of me.

      In our version of SAP Business One the final invoice receives in the Remarks/Call Notes the following statement:

      <blockquote>Based On Sales Orders 116.<br />

      Based On Deliveries 102.</blockquote>

      With that I decided to use the <b>Substring</b> calculation in the formula editor of the Print Layout Designer to get at the Sales Order # embedded in the Remarks field. The only problem with using the basic SubString call is that this will not work if a CSR (Customer Service Rep) may put call notes in this field on the original sales order. For those notes, will push the above sample text to the end of the string. With that said, I decided to play with the formula editor to see if I could start at the end of the call notes string to get at the needed Sales Order #. Here is what I came up with and it works like a charm:

      Substring(Field_155,Length(Field_155) - 30,Length(Field_155) - 26) <-- outputs: 116

      In the above code; I targeted <b>Field_155</b> (my call in the Print layout designer to he Remarks column in the database) and I then used the <b>Length</b> formula to find the end of the Remarks string and quickly subtracted 30 to find the start of the Sales Order Number. Then, following the same technique I used Length to find the end of the string again and subtracted 26 to find the end of the Sales Order #.

      I hope this helps someone else in the future,

      Nate Hanna

  • author's profile photo Former Member
    Former Member
    Posted on Apr 17, 2007 at 08:55 PM

    Can anyone help me with a similar problem I am having with B1 and an "add-on"? I need to include a field on the invoice that does not exist in the core B1 tables. I have looked at the "External Data" field, but when I use Source Type "Database" I can not figure any way to point it to an external data source. Ealier in the point running a SQL query was discussed, but I don't think it explained where/how to do that. Can I use the "Formula" Source Type somehow? I can have a Stored Procedure in the "add-on" database or the B1 database, but I am not familar with what I need to do so I can provide B1 with a *.dll that would run it.

    Thank You

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.