This is a two-parter: I’ve created a new form which contains the Vendor Ledger Entry form as a sub-form. When run, I need this new form to display all open invoices, listed by vendor. The user advances to the next (or previous) vendor and associated open invoices by selecting the appropriate arrow button on the toolbar, as usual. I had something sort of working using the SubFormLink and SubFormView properties of the new form’s subform object. However, the resulting filtering of the Vendor Ledger Entries table was so slow, it wasn’t useable. This doesn’t seem like it should be so difficult - what am I missing here? Would a tablebox (instead of a subform) to the Vendor Ledger Entries table be more appropriate, and if so, how is a tablebox filtered to do what I want? The second question deals with checkboxes. We have added an additional boolean field to the Vendor Ledger Entries table. The same form mentioned above needs a button that when pressed, allows the user to place a checkmark in this new field for all the open invoices displayed for that vendor. I’ve done a REPEAT UNTIL NEXT loop, setting the field to TRUE as I go. Monitoring the code reveals that the loop is operating properly, but a check mark is placed only in the field of the currently selected record. Thanks in advance for any assistance offered.
I hope I understand correctly: You display Vendor Ledger Entries with a filter on Vendor and Open, correct? Well, for question 1: make sure to have the key of the VLE table set to (“Vendor no.”,open) Second question: The following single statement put on the OnPush trigger should do the trick: MODIFYALL(“new check box variable”,true); Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch
Thanks, Marcus! The ModifyAll function does the trick! I feel silly for something so obvious, but I’m too new to Navision to be intimately familiar with all the functions. I know most of my questions are probably pretty fundamental. As far as the first part of my question, I am using SETCURRENTEY/SETRANGE statements to give me the desired results in a tablebox (I’ve abandoned the subform approach). But one problem I can’t resolve is that the resulting tablebox usually lists a single vendor more than once if that vendor has several open invoices. I would like to see more of a summary, i.e. only one vendor per line, with the amount field showing a total for all of that vendor’s open invoices. I’ve seen statements similar to SETRANGE(“Vendor No.”, “Vendor No.”) used elswhere to remove duplicates from the resulting list, but it doesn’t work here. Also, I’ve already got a flowfield summing the amounts that I’m using as the SourceExp on a separate textbox, which I could re-use for the tablebox amount once I summarize the list by vendor (this also has the added benefit of allowing a drill-down to show all of that vendor’s open invoices). Thanks again! You’ve been a great help!
Using the Vendor Ledger Entry table is the wrong approach if you want to summarize open vendors only. Use the Vendor table instead. You also don’t have to create a flow-field as this field already exists : “Balance”. Simply set a filter to “Balance <>0” in order to get a list with vendors with open Balances. Note: This list would NOT work if the vendor has open Ledger entries but the balance is zero. Such as Open invoices and open Credit Memos which sum up to zero! Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch
About the first part of the question, the problem could be that you haven’t defined (and use) a correct key for the filtering you’re doing. Instead of default key when choosing the currentview property values, choose a more appropiate one (for example, “Vendor,Registering Date”, key that you would previously define on the open invoices table. About the second one, i must suppose that your are doing a code like this: IF (Record.FIND(’-’)) THEN REPEAT Field := TRUE UNTIL Record.NEXT=0; Record.MODIFY; With this code you just modify the last record, as the modify function is just done on the last one. As Marcus told you, modifyALL should work fine, but sometimes it’s not as good option as using the loop (when you have some exceptional cases where you won’t apply the value). The correct code should be as follows: IF (Record.FIND(’-’)) THEN REPEAT // If you got any exceptional case, you should get the // conditions here: // example: // IF (DoesMatchMyRequirements(Record)) THEN Record.Field := Value; Record.MODIFY (TRUE); // Modifying the record inside the loop, so the record where // we changed the value is saved (modified) before obtaining // a new record. UNTIL (Record.NEXT = 0); That should work as Record.MODIFYALL(Field,Value)… Alfonso Pertierra apertierra@teleline.es Spain
Thank you, Marcus and Alfonso, for replying. Marcus, I’m pretty much doing what you suggested in your last response by using a form based on the Vendor table, with a subform showing only that vendor’s open entries. Using the ‘Balance’ field does have the desird effect you mentioned, but as you state, I need to guarantee this operation even if an open credit memo zeroes out any open invoice. Another undesired thing is that when I page through to each vendor, all vendors are displayed whether they have any open ledger entries or not. The manual mentions using table relations to get data to propagate from one table to another, but no examples were given. If I created a new boolean field in the Vendor table, what would I need to do to get any ‘TRUE’ values from the ‘OPEN’ field in the Vendor Ledger Entry table to set the new Vendor field? If I could do this, then I could use this field to: 1)Step through only those vendors with open entries, and 2)Ensure that vendors having any credit memo/invoice ‘zeroing’ would also be displayed. I’m not sure if setting a table relation would do this. Any information on how to accomplish this would again be greatly appreciated, as well as any good examples of propagating data from one table to another using table relations. Thanks.
Create the boolean field as mentioned, Set the following properties: FieldClass = FlowField CalcFormula= Exist(“Vendor Ledger Entry” WHERE (Vendor No.=FIELD(No.),Open=CONST(Yes))) Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch