Export fields from 2 tables with XMLport

Hi,

I have created a XMLport that exports a number of records with 6 attributes. All the 6 attributes are from “Sales Cr.Memo Header” BUT the last attribute needs to be from “Sales Cr.Memo Line”!?

Link:

WHERE “Sales Cr.Memo Header”.No. = “Sales Cr.Memo Line”.“Document No.”

How can this be done. I haven’t found a solution on the forum yet?

Best Regards

//Mads…

What happens when there is more than one line?

What? I don’'t understand?

Hi Mads,

what exactly is it, that you want to export? Is it the Credit Memo Lines (with some Information from the Credit Memo Header)? If not, what do you want to do, if there are more than one Lines to your Header?

If you want to Export the lines, you have to pick them as the Record in the XML-Port, and get the extra information from the head.

In the XML Port Designer, you define the Node as “Source Type” Text (so it’s a Variable), and then you add some code in the Export::OnBeforePassVariable-Trigger of that Node:

CRHead.GET(“Document No.”);
Node := CRHead.“Bill-to Name”;

You said you have 6 attributes, and one of them is from the line, which you want to export as part of an XMLPort that runs off of the Credit Memo Header table. You got the table relationship right there, but a single credit memo header can have multiple lines. It has what we call a “one-to-many” relationship. If you have more than one line on the credit memo, then each line could have a different value for your 6th attribute. How are you going to decide from which line to take the attribute?

Also, about Daniel Z’s solution. I do think you need to go with a text element, but I would not put the code in that trigger. I would put the code in the credit memo header’s OnAfterGetRecord trigger, so that you can insert other elements as you need them, without having to move code around, or do the same thing more than once. Better get the data in a central place as much as possible.

Yes you are correct it is a one-many relationship. The thing is that the attribute on the Sales Cr.Memo Line table is the same for every line to 1 header.

I was hoping that I could create a FINDFIRST or something like that.

If I shall try Daniel Z’s solution. Could you then make a step-by-step procedure - i am pretty new to this stuff?

Hi Mads, I’ll try :wink:

You have your XML-Port with your Credit Memo Header as an Element, and then six attributes, 5 of them configured as Fields of the Header Table (so the Source Type is Field, and there is a relationship to a field in the Credit Memo Table), and one of them as a Text-Attribute (so Source Type is Text). This Text-Attribut (let’s call it LineInfo) is now a Variable, that you can use in the Code.

You also have to set up a Global Variable (CreditMemoLine of Type Record for the Credit Memo Line Table). Now you can add the following code (best place is probably the Export::OnAfterGetRecord Trigger of your Credit Memo Header):

CreditMemoLine.SETCURRENTKEY(“Document No”.,“Line No.”);
CreditMemoLine.SETRANGE(“Document No.”,CreditMemoHeader.“No.”);
IF NOT CreditMemoLine.FINDFIRST THEN
LineInfo := ‘’
ELSE
LineInfo := CreditMemoLine.Field;

Hmm, just a question, though. If the attribute is the same for all Lines to one Header, wouldn’t it make more sense to put that information in the Header anyway?