I need a little explanation of the logic in C/SIDE. I am used to querying in SQL, and am trying to get an understanding of how things get done in Navision. here is my problem. I think it is simple in SQL, but Im scratching my head in C/SIDE: I have the Purchase Header table: fields that matter: DocType, No., Vendor Order No. I have the Purchase Line table: Fields: Document No. (Matches “No.” field in PH table), Quantity I have a User Table: Fields: Vendor Order No. (Matches "Vendor Order No. in PH table) I want to have a field on the User Table display the sum of all Qty’s in the purchase line table that are associated with a Purchase Header record with the same Vendor Order No. as the User Tables current record. So you see, I need to resolve the Purchase Header lines with the Vendor Order Number before I can get the correct Purchase lines to Sum. Coming from a SQL background I am trying to fit it all into one Flowfilter CalcFormula. But Im quite sure it doesnt work that way. Lastly, I hear for performance it is good to set up a boolean flowfield to check if any records exist in the Purchase line table first, before wasting the processing of the sum formula. I have the boolean flowfield working, but not sure how to implement it. Thanks for any help.
Hi,
quote:
But Im quite sure it doesnt work that way.
You’re right you cannot “jump” over a table (in your case the Purchase Header) to define a flowfield [V] I guess the easiest solution (with less coding) would be to include the field “Vendor Order No.” in the Purchase Lines, which get copied from the Purchase Header. This way, you wouldn’t have to play around with the boolean flwofield aswell. Regarding the boolean flowfield, it is actually not necessary to check a boolean flowfield first. The FlowField technology is designed that way, that the value (sum) gets calculated with 2 database read transactions and the difference of these values is the desired result. Therefore the boolean check is not necessary because the flowfield calculation does not depend on the numbers of records to be summed up… Have a look at chapter 20 of the Application Designer’s Guide, that should give you some ideas on how it works. [;)] Saludos Nils
Thanks, Im going to read it now. If I were not going to add the field to the Purchase Line table, what would be the correct way to do it? when you say “(with less coding)” are you saying there may be need for C/AL code? Im fine with that if that is what it takes. -----To show what I was trying, and realize doesn’t work…----- sum(“Purchase Line”.Quantity WHERE(DocType=CONST(Order), Document No.=(Filter(“Purchase Header”.No. WHERE (Vendor Order No.=FIELD(Vendor Order No.)))))) The filter part is impossible I’m sure, its just the way I would imagine it. If it could return multiple rows like a subquery my sum would be of all Line items from all PO’s with the Vendor Order number I have in the current record of the user table. ------------------------------------------
Coming from a SQL Background it sounds right, but flowfields have the limitation that you have to filter directly on the 2 tables involved, you may not used variables that may change during runtime. In case you don’t want to add the Vendor Order No. to the Purchase Lines, the most convenient solutions depends on where you need this value and if you need the DrillDown. If it’s a report than sum up with 2 record variables, one for the Purchase Header and one for the Purchase Line, in this case you don’t need any Drill Down functionality. If you want this value on a form, then definitly a flowfield is the most easiest solution to choose. Otherwise you’ll have to recalc everytime a user changes the record and that might slow down the form considerably. Saludos Nils
Hello Franklyn, If I have understood your requirements there should be a fairly simple solution using a flowfield. On your user table create a new field to store the foreign key to the relevant purchase document. This new field should have a fieldclass of ‘Flowfilter’. Create your flowfield which sums the quantity from the purchase line table. In your filter expression you will include Purchase Line document number = FlowFilter Fieldname on your user table. Wherever you implement this table you will need to find the relevant Purchase Header document number via the Vendor Order Number, then use a setrange on your flowfilter field passing in the Navision Document number from the Purchase Header Table. Having used Calcfields (if appropriate) your quantity field should display the relevant information. Hope this helps.