FIND BO based on Other BO

[MESSI]

Hi Folks,

Please advice:

Screenshot_3.jpg (38.65 KiB) Viewed 36316 times

BO_InvoicesLines is reference to BO_Invoices
VAT is Reference to BO_InvoicesLines

----------------------------------------------------------
VATSummary records are the results.

I have this process:

Code: Select all

FIND InvoicesLines WHERE (InvoicesLines IN Invoices.LineItems)
FIND VAT WHERE (InvoicesLines.ps_VATpercent=VAT)
CREATE VATSummary FOR EACH VAT WITH VATSummary.VAT=VAT.Name, VATSummary.SUMVAT = SUM InvoicesLines.VATAmount WHERE (VAT=InvoicesLines.ps_VATpercent),VATSummary.InvoiceMaster=Invoices

BSV Example : https://file.io/kXLCVrEd6PgV

The process Finds the 4 lines of the Invoice, but only 1 record of VAT.

[himanshu]

put your last two lines into separate process, having input of invoices

FIND VAT WHERE (InvoicesLines.ps_VATpercent=VAT)
CREATE VATSummary FOR EACH VAT WITH VATSummary.VAT=VAT.Name, VATSummary.SUMVAT = SUM InvoicesLines.VATAmount WHERE (VAT=InvoicesLines.ps_VATpercent),VATSummary.InvoiceMaster=Invoices

[MESSI]

Thank you very much. I will try it!

[Jaymer]

put your last two lines into separate process, having input of invoices

FIND VAT WHERE (InvoicesLines.ps_VATpercent=VAT)
CREATE VATSummary FOR EACH VAT WITH VATSummary.VAT=VAT.Name, VATSummary.SUMVAT = SUM InvoicesLines.VATAmount WHERE (VAT=InvoicesLines.ps_VATpercent),VATSummary.InvoiceMaster=Invoices

Hey buddy. Hope you’re coming to FL.
While the concept of a subprocess is something that he was definitely not understanding yet,
I don’t think this is gonna work.
You said it’s an input of invoices.
But it Has to be an input of invoiceLlnes the way the first find is written.

If that’s the case, then it’s gonna call the subtask 4 times. And that’s going to create 4 summary lines.
This complexity is because we can’t do a find unique in aware anymore.

I can think of four ways to do this, but they’re all convoluted, or require a stored procedure, which is the ideal way to do it in my opinion.
But given what you have written, I don’t see how the create summary is going to execute only two times.

And because we cannot get the unique VAT values in the invoice lines file, easily, I don’t see how you can create a context in a parent to call this sub only two times.

[MESSI]

Many thanks to Himanshu (he helped me with this in less than 5 minutes ):

Process 1: (input invoices)
FIND VAT WHERE (EXISTS InvoicesLines WHERE (InvoicesLines IN Invoices.LineItems AND InvoicesLines.ps_VATpercent=VAT))
process_sub_vatSummary


Process 2: (input: Invoices, VAT)
FIND VAT WHERE (InvoicesLines.ps_VATpercent=VAT)
CREATE VATSummary WITH VATSummary.VAT=VAT.Name, VATSummary.SUMVAT = SUM InvoicesLines.VATAmount WHERE (VAT=InvoicesLines.ps_VATpercent AND InvoicesLines.InvoiceOwner=Invoices),VATSummary.InvoiceMaster=Invoices

Also here it's the example BSV, if someone needs it: https://file.io/upcMKVeM0XTA

[Jaymer]

Cool. That makes more sense. Neat!

Because it’s a reference field, it’s finding two linked VAT records.
So in this case it does find the unique VAT records that exist in an order’s lines.
But if you had customer data, you couldn’t use this to find the unique cities, for example.
… Unless the city field was a reference Field to a city master table.

[himanshu]

put your last two lines into separate process, having input of invoices

FIND VAT WHERE (InvoicesLines.ps_VATpercent=VAT)
CREATE VATSummary FOR EACH VAT WITH VATSummary.VAT=VAT.Name, VATSummary.SUMVAT = SUM InvoicesLines.VATAmount WHERE (VAT=InvoicesLines.ps_VATpercent),VATSummary.InvoiceMaster=Invoices

Hey buddy. Hope you’re coming to FL.
While the concept of a subprocess is something that he was definitely not understanding yet,
I don’t think this is gonna work.
You said it’s an input of invoices.
But it Has to be an input of invoiceLlnes the way the first find is written.

If that’s the case, then it’s gonna call the subtask 4 times. And that’s going to create 4 summary lines.
This complexity is because we can’t do a find unique in aware anymore.

I can think of four ways to do this, but they’re all convoluted, or require a stored procedure, which is the ideal way to do it in my opinion.
But given what you have written, I don’t see how the create summary is going to execute only two times.

And because we cannot get the unique VAT values in the invoice lines file, easily, I don’t see how you can create a context in a parent to call this sub only two times.

Hopefully will see you again my friend!