I would like to know how to change company on a subform, if it can be done at all. A more detailed explanation follows. Thanks, Alastair I have three companies with a shared Customer table, where the Cust. Ledger Entries table is “per company”. I have a main form for Customers, with a lookup for Company and a subform for Customer Ledger Entries. I want the ledger entries to be from the company chosend in the lookup. I have tried a few ways of doing this (CHANGECOMPANY, SETTABLEVIEW – which I now know doesn’t work), and spent a couple of hours searching through Navision.net, without success. Any pointers or suggestions would be appreciated. If it makes it any easier, it would be enough for me to know how to have three subforms, where subform A always shows the ledger entries for ‘Comp. A’, subform B for ‘Comp B’, etc.
Did you try using CHANGECOMPANY in trigger OnOpenForm of the subform form? Elena
Elena, thanks for your help. That works for the second problem (with three subforms). For the original question, it’s enough to put CHANGECOMPANY in OnActivateForm. I don’t know why it didn’t work when I tried a few days ago; maybe I had been trying too many things at the same time. There are two lesser issues that remain. First, with OnActivateForm, I have to click on the subform for the company to change (Customer changes, by contrast, immediately update the subform). So let’s say I’m on Customer 10000 and Company A. If I change to Company B, nothing changes in the subform. If I then change to Customer 20000, I see the entries for 20000 in Company A. If I then click on the subform, it shows the entries for Company B (for Customer 20000). This may have to do with the second issue - how to tell the subform what company has been chosen in the main form. After various failed approaches, I set up a CompanyTemp table, and a variable CompanyTempRec for this table. In the main form, the company lookup has Tablerelation = Company SourceExpr = CompanyTempRec.CompanyName In its OnAfterValidate code there is: CompanyTempRec.DELETEALL; CompanyTempRec.INSERT; COMMIT; and in the subform I have IF CompanyTempRec.FIND(’-’) THEN CHANGECOMPANY(CompanyTempRec.CompanyName); CompanyTemp only ever contains one record, and will not usually be accessed more than once a second, so I don’t see performance issues, but there may be a neater solution. Again, any comments are appreciated. Alastair
Firstly, whilst it seems simple, it is not a good idea to make the master records (Cust, item etc) as common to all companies, it just causes too many problems. Better is to write code that syncs them between all the companies, which is a really simple bit of code. As to your actual question, this is real simple. Lets say you were logged into company A looking at a customer in company B. Just add a new field (Company Filter) create as class flowfilter and link to company name. Add the field company to the ledger table. Add the two fields to the properties in your flowfield. Add the following code to your ledger entry subform If Company.get(getfilter("company name")) then rec.changecompany(company.name);
David, I tried to sync the tables for different companies, and ended with a mess. I can’t say I understood your code either (the flowfield, specifically), which is probably more because I’m a beginner than anything else. The client asked for a report – I’m trying out the form as practice as I just started Navision programming a few weeks ago. For what it’s worth, here’s the code I used to sync (in OnInsert). CurrentCompany - record and table to store a flag AlterationSuccessful – Boolean, to know when to undo things IF CurrentCompany.ISEMPTY THEN BEGIN Rec.LOCKTABLE; CurrentCompany.LOCKTABLE; CurrentCompany.Name := COMPANYNAME; CurrentCompany.INSERT; AlterationSuccessful := TRUE; IF Company.FIND(’-’) THEN REPEAT IF Company.Name <> CurrentCompany.Name THEN BEGIN CHANGECOMPANY(Company.Name); AlterationSuccessful := Rec.INSERT(TRUE); END; UNTIL (Company.NEXT = 0) OR (NOT AlterationSuccessful); IF NOT AlterationSuccessful THEN; //put code to delete record CurrentCompany.DELETEALL; COMMIT; END; I’ll try to debug it again when I’m not doing other things, though the “PerCompany = No” still looks appealing. What sort of problems does this lead to? Thanks. Alastair
quote:
Firstly, whilst it seems simple, it is not a good idea to make the master records (Cust, item etc) as common to all companies, it just causes too many problems.
David, could you please elaborate on this a bit?
quote:
Originally posted by afarr
David, … I just started Navision programming a few weeks ago. Alastair
Please don’t say you have just started in Navision, and you changed the table structure of core tables. please…
Steffan, there are many issues, mainly related to dynamic data stored on master records eg. Last purchase cost on the item card. Then there is the backup issue. I’ve seen it done, and I’ve seen it work, I’ve also seen it done and fail. If you know what you are doing, it can be made to work, but not for a beginer.
quote:
Originally posted by David Singleton Please don’t say you have just started in Navision, and you changed the table structure of core tables. please…
All my work is on tables in the 50000’s (either dummy tables, or copies of the original Navision tables). Alastair
I must agree to David: Setting Customer to DataPerCompany=No seems to be simple, but it surely is not. In order to have consistent data, you must also set all the tables to DataPerCompany=No, to which fields in the customer table refer to. An example would be the Gen. Bus. Posting Group table. If you do not do that and you set up different Posting Groups in the companies, the validation of that field in table customer will fail. That need to make many more tables valid for all companies can lead to serious problems. Take the Payment Terms table. This is valid for customers AND vendors. If you make customers DataPerCompany=No than the Payment Terms table will have to be DataPerCompany=No as well. Then you cannot set up different payment terms for your vendors per company which you still might want to. Synchronizing is generally the better way to do it. I developed it once for one customer with DataPerCompany=No for Customers and Vendors. We ended up with a mixture between setting tables to DataPercompany=No (about 50 tables, that was Financials!) and synchronizing other tables that could be set to DataPerCompany= No (for example the Comment Line table, that holds records for many other tables as well). All in all it worked, the client is still happy with the solution, but a pure solution with just synchronization would not have been much more work, I think. A technical solution for your subform, Alastair: In your subform create a function “ChangeCompAndUpdateForm(CompName)”, parameter is CompName, text 30: Changecompany(CompName); CurrForm.Update; In your main form (SourceTable=Customer) create a global variable: CompName Text 30 Create a TextBox with SourceExpression CompName and TableRelation to Company.Name. In the OnAfterValidate-Trigger write: CurrForm.NameOfSubform.FORM.ChangeCompAndUpdateForm(CompName); That should do the trick. The problem is, that you cannot call the UPDATE function on a subform from a main form. Therefore you have to create function inside the subform that does the UPDATE on the subform and can be called from outside.
Hi Beaver, thanks for your explanation, and for your solution. I only got round to trying it out this morning - it’s simple and does exactly what I wanted. Alastair