FlowField - Where Field doesn't equal current document no.

On the transfer orders line I’m trying to create a flow field that shows if this is currently already on a transfer order. So I created a new field in the transfer line table, type flowfield.

Sum(“Transfer Line”.Quantity WHERE (Transfer-to Code=FIELD(Transfer-to Code),Item No.=FIELD(Item No.)))

This shows what they are requesting on this transfer, so I need to be able to filter out the current document number. Any ideas? Thanks! Kind of like this:

Sum(“Transfer Line”.Quantity WHERE (Transfer-to Code=FIELD(Transfer-to Code),Item No.=FIELD(Item No.),Document No.<>FIELD(Document No.)))

Jason

Better to go through OnLookup trigger…

What do you mean? Can you give me an example? Thanks.

Something Like

Table 5741

Existing Quantity - OnLookup()

lTransferLine.RESET;

lTransferLine.SETRANGE(“Item No.”,“Item No.”);

lTransferLine.SETFILTER(“Document No.”,’<>%1’,“Document No.”);

FTransferLine.SETTABLEVIEW(lTransferLine);

FTransferLine.SETRECORD(lTransferLine);

FTransferLine.RUNMODAL;

Where Existing Quantity is new field

lTransferLine - Record-Transfer Line

FTransferLine-Form-Transfer Lines

Form 5741

Form - OnAfterGetRecord()

“Existing Quantity” := 0;

lTransferLine.RESET;

lTransferLine.SETRANGE(“Item No.”,“Item No.”);

lTransferLine.SETFILTER(“Document No.”,’<>%1’,“Document No.”);

IF lTransferLine.FINDSET THEN

REPEAT

“Existing Quantity” := “Existing Quantity” + lTransferLine.“Outstanding Quantity”;

UNTIL lTransferLine.NEXT = 0;

Where lTransferLine - Record-Transfer Line

No that is absolutely not how I would do it. First, OnLookup is to look up data, so that is not the right place to put that code. Second, I would never put any data code in the form like that. The code itself looks pretty good, but the placement are two things that I consider to be big mistakes.

What I would do is create a function in the Transfer Line table, called QtyOnOtherOrder or something like that, with a Decimal type return value. Use Mohana’s code to calculate the value, and tweak it until it provides you the right value. Then in the subform on the transfer order, you can then add a column in the list (select textbox from the toolbox, then click inside the tablebox), and set the SourceExpr of that column to the Transfer Line’s new function.

If you want to be able to drill down into the values further, then I would put that code in another function, also in the Transfer Line table called QtyOnOtherOrderDrilldown, and place a call to it from the form’s OnDrillDown trigger of the new textbox.

Now you have functionality that you can put on any form, or any page, and it will always be the same, regardless of which form the data is on.

This is going off of the top of my head so details might need to be tweaked, but the point is not to program all of that logic on the one form.

DenSter,

I did what you said and it works great. Well except that now it is slow for me to search the transfer orders. I made sure that I had keys in Transfer Line and Purchase Line that matched what I was filtering on.

Here is the code I use to get the amount on a transfer order, excluding the one I am working on now.

TotalQty := 0;
TransferLineTable.RESET;
TransferLineTable.SETRANGE(“Item No.”,“Item No.”);
TransferLineTable.SETFILTER(“Document No.”,’<>%1’,“Document No.”);
TransferLineTable.SETFILTER(“Transfer-to Code”,“Transfer-to Code”);

IF TransferLineTable.FINDFIRST THEN BEGIN
REPEAT
TotalQty += (TransferLineTable.Quantity - TransferLineTable.“Quantity Shipped”);
UNTIL TransferLineTable.NEXT = 0;
END;

But you didn’t set the key using SETCURRENTKEY.

So like this?

TotalQty := 0;
TransferLineTable.RESET;
TransferLineTable.SETCURRENTKEY(“Item No.”,“Document No.”,“Transfer-to Code”);
TransferLineTable.SETRANGE(“Item No.”,“Item No.”);
TransferLineTable.SETFILTER(“Document No.”,’<>%1’,“Document No.”);
TransferLineTable.SETFILTER(“Transfer-to Code”,“Transfer-to Code”);

IF TransferLineTable.FINDFIRST THEN BEGIN
REPEAT
TotalQty += (TransferLineTable.Quantity - TransferLineTable.“Quantity Shipped”);
UNTIL TransferLineTable.NEXT = 0;
END;

That didn’t seem to help

You should also do a FINDSET instead of a FINDFIRST. Should be faster.

Helping and seeming to help are two different things. How many records is it looping through? What sort of measurement did you do to verify? There’s not a lot of detail to go on. If it’s going through 100 records it will be slower than going through 2.

And remember, if this is displayed on a form, every time you look at an order it is going to calculate for all of the lines. That is going to take time.

I would have gone the FlowField route. I know you can’t do a not equal directly, but you can create an additional FlowFilter field and use that in the calculation.

I did have FINDSET but tried FINDFIRST. I will change it back, but I doubt that is the issue. We have a bunch of records in that table so I’m sure that is some of the problem. Maybe I will try going the flowfield route. Is that were I make it as a filter? and then use that in another flowfield?

Take a look at fields 64 to 68 in the Item table. They are FlowFilters, and the last one is Inventory or Quantity on Hand. Look at the properties of them all for examples of how to make FlowFilters and use them in your FlowField.

You’ll have to do a SETFILTER on your FlowFilters on the form after you retrieve the record.