I’m using the C/Front system with .NET (CFrontDotNet) and I’m running into problems filtering a table on two or more columns. I know I can’t use the SetFilter() method twice as it clears existing filters. I would use the SetView() method, but I don’t know the “query” to achieve this goal.
I’ve looked at the post titled Filter Field1 OR Field2. But I really don’t want to go into MARK’ing records and creating temporary tables just seems like overkill to me.
What I’m trying to do is mimic the ‘Sales Invoice’ by retrieving the data, putting into XML and then applying an XSL stylesheet. Everything is fine, but I’m running into this problem when attempting to retrieve the “Shipment” lines for the line items. I’m trying to get the info from the ‘Sales Shipment Line’ table.
I think you have read the help wrong. The help on SetFilter() says if you use it twice on the same field, it will remove any previous filters. If you use on Field1 and then Field2, it will not remove the filters. I have used it several times before on 2 or more fields. SetFilter() should work for you the way you want it to without using Mark or creating a temporary table.
Damn it! Don’t you just hate it when you make stupid mistakes. What’s worse is when those mistakes are posted on a public forum. Doh!
Can someone tell me which table the Sales Invoice report gets it’s shipment information from? For each line item in the report, there may be a “Shipment” line, we’re looking to replicate this report, and therefore; this behaviour. I can’t get to the report Designer, so I’m left looking at the C/FRONT Sample Application to look through the raw data.
That’s not how it works in Navision. In Navision, a Sales Order Has a One to Many relation to a created Sales Shipment, and the same sales order has a one to many relationship to a Sales Invoice. Thus you are left with a Many to Many relationship between Shipment and Invoice.
If you see a “Shipped” field in the invoice line, then its probably a customization. There really is no general solution as to how to do this. I have done it many times for clients, but its always a specific mod that works with their business practice only.
In principle, its not possible to answer your actual question, without knowing where this “Shipment” line comes from.
At the moment, I have replicated everything that the Sales Invoice report does, apart from the Shipment lines under the relevant line items. I’m using the Sales Shipment Header and Sales Invoice Line tables. The only difference is that when I look under “Sales & Marketing” → History → Posted Invoices, some of their line items don’t have a shipment history, but when I run the same invoice, I can get the shipment lines. I can’t seem to find the condition or set of conditions that the Sales Invoice report uses to decide whether to print out “Shipment” lines for line items.
At the moment, the filters I’m using to find the shipment line for the line item is a little dodgy. I’m using the fields “No.”=line item code, “Order No.”=invoice order number (if there is one), “Sell-to Customer No.”=invoice customer number, “Gen. Prod. Posting Group”=RETAIL, and “Shipment Date”=invoice shipment date to filter the results for the current line item.
``
If Navision can do it, and I have access to all the data Navision does, then I should be able to replicate the Sales Invoice report. I just don’t have access to the “Design” feature and can only try to replicate the report by using the C/Front .NET sample app to see where the data is.
Just to let you know, I’ve seemingly managed to replicate the “Shipment” lines that the Sales Invoice produces.
I’m only adding Shipment lines from the Sales Shipment Line table if they contain an Order No., have more than one shipment line for the invoice line item or the shipment date is different to that of the line items shipment date.
Here’s the code I’m using:
`
// Now get the shipment line items
int shipmentTableHandle = CFront.OpenTable(CFront.TableNo("Sales Shipment Line"));
int shipmentRecordHandle = CFront.AllocRecord(shipmentTableHandle);
// Don't get shipment info for invoice line items with empty order numbers
if (!String.IsNullOrEmpty(invoice.OrderNumber))
{
try
{
// Set filter data
CFront.SetFilter(shipmentTableHandle, CFront.FieldNo(shipmentTableHandle, "No."), item.Code);
CFront.SetFilter(shipmentTableHandle, CFront.FieldNo(shipmentTableHandle, "Order No."), invoice.OrderNumber);
CFront.SetFilter(shipmentTableHandle, CFront.FieldNo(shipmentTableHandle, "Sell-to Customer No."), invoice.AccountReference);
CFront.SetFilter(shipmentTableHandle, CFront.FieldNo(shipmentTableHandle, "Gen. Prod. Posting Group"), "RETAIL");
CFront.SetFilter(shipmentTableHandle, CFront.FieldNo(shipmentTableHandle, "Shipment Date"), item.ShipDate.ToShortDateString());
CFront.SetFilter(shipmentTableHandle, CFront.FieldNo(shipmentTableHandle, "Quantity"), ">0");
if (CFront.RecordCount(shipmentTableHandle) > 0)
{
CFront.FindFirstRecord(shipmentTableHandle, shipmentRecordHandle);
do
{
ShipmentLineItem shipItem = ExtractShipmentLineItem(shipmentTableHandle, shipmentRecordHandle);
// Only add the shipment line if there are more than shipments, or the one shipment was shipped on a different date that the one proposed.
if ((CFront.RecordCount(shipmentTableHandle) > 1) ||
((CFront.RecordCount(shipmentTableHandle) == 1) && (item.ShipDate.Date != shipItem.ShipDate.Date)))
{
item.ShipmentLineItems.Add(shipItem);
}
} while (CFront.NextRecord(shipmentTableHandle, shipmentRecordHandle, 1) != 0);
}
}
finally
{
// Clean up
CFront.FreeRecord(shipmentRecordHandle);
CFront.CloseTable(shipmentTableHandle);
}
}
`
This seems to give the same results as Navision’s Sales Invoice report. If anyone spots anything wrong, you’re more than welcome to point it out. Thnx alot for all your time.
Hi Brent, maybe there is a little bit of confussion here.
In the Sales Invoice Report, the use of the Shipment Line, is NOT to get shipment information, (even though it may appear like that). It is in fact trying to work out what was the quantity on the original Sales Order. Basically the code says
If the sales order still exists, then I can get Order quantity from the Sales Order, so thats enough, but if the Sales order is fully shipped and invoiced and Deleted, then I can assume that the total of all the shipments for that line are the same as the originally ordered quantity.
This is unfortunately wrong in many ways, two simple things that ruin this math are 1/ Part ship, and then delete the remaining un-shipped line. Or just change the quantity on the order. A more complex issue arises if the user deletes a line on an order after it is shipped and then reuses the same line to ship a different item.
In your case it looks like you are trying to work out what quantity of shipped items are invoiced by this particular invoice, but this would be wrong since you are seeing ALL the shipments from the order, not just the one for this invoice.
Now having said that, its quite possible that business rules in your business mean that this works and gives the numbers you need. Just be aware that its not a generic solution, and needs to be well tested.
Nice one David, I have wondered for some time the meaning of all that code. I use none of it and have the Sales order No. and Sales order Line no., Posted Shipment No. and Posted Shipment Line No. populated on every line including the invoice lines using “Get Shipment” as a way of trying to keep track of it all from the Invoice line perspective. You need to add some fields to achieve this though.