{"action":"created","issue":{"url":"https://api.github.com/repos/volkanceylan/Serenity/issues/852","repository_url":"https://api.github.com/repos/volkanceylan/Serenity","labels_url":"https://api.github.com/repos/volkanceylan/Serenity/issues/852/labels{/name}","comments_url":"https://api.github.com/repos/volkanceylan/Serenity/issues/852/comments","events_url":"https://api.github.com/repos/volkanceylan/Serenity/issues/852/events","html_url":"https://github.com/volkanceylan/Serenity/issues/852","id":163007514,"number":852,"title":"MultiTenant Lookup editor - Input String incorrect format","user":{"login":"sayuga","id":16759829,"avatar_url":"https://avatars.githubusercontent.com/u/16759829?v=3","gravatar_id":"","url":"https://api.github.com/users/sayuga","html_url":"https://github.com/sayuga","followers_url":"https://api.github.com/users/sayuga/followers","following_url":"https://api.github.com/users/sayuga/following{/other_user}","gists_url":"https://api.github.com/users/sayuga/gists{/gist_id}","starred_url":"https://api.github.com/users/sayuga/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sayuga/subscriptions","organizations_url":"https://api.github.com/users/sayuga/orgs","repos_url":"https://api.github.com/users/sayuga/repos","events_url":"https://api.github.com/users/sayuga/events{/privacy}","received_events_url":"https://api.github.com/users/sayuga/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-06-29T19:31:42Z","updated_at":"2016-06-30T19:24:54Z","closed_at":null,"body":"I've been playing around with this one and could use some outside help. I keep getting an \"input string is in incorrect format\" error. I cant seem to figure out the fix. \r\n\r\nI've set up as a multitenant. I have a table which acts as my master table. At that table I have a TenantId that is defaulted to 1, thus only one tenant can see data in that table. I also have another ID, CustomerID, which I have joined to the TenantId from the tenants table. I use this so that I can use the CustomerID to designate the TenantId in the SqlViews thus, create reports which clients can only see the data pertaining to them. Those views work perfectly. \r\n\r\nI created the following lookup script to deal with the search box: \r\n\r\n [LookupScript(\"FMCorp.MatrixCustomerName\")]\r\n    public class MatrixCustomerNameLookup : MultiTenantRowLookupScript<MyRow>\r\n    {\r\n        Field fld = MyRow.Fields.CustomerName;\r\n\r\n        public MatrixCustomerNameLookup()\r\n        {\r\n            IdField = TextField = fld.PropertyName;\r\n        }\r\n\r\n        protected override void PrepareQuery(SqlQuery query)\r\n        {\r\n            query.Distinct(true)\r\n                .Select(fld)\r\n                .Where(\r\n                    new Criteria(fld) != \"\" &\r\n                    new Criteria(fld).IsNotNull()\r\n                    );\r\n            AddTenantFilter(query);\r\n        }\r\n        protected override void ApplyOrder(SqlQuery query) { }\r\n    }\r\n\r\nMatrix row designates the fields affected like this: \r\n\r\n   [DisplayName(\"Customer Id\"), ForeignKey(\"[dbo].Tenants\", \"TenantId\"), LeftJoin(\"cust\"), Width(100), AlignCenter] //Join TenantID from Tenant Table to Matrix Table. Identifier is \"cust\"\r\n        [LookupEditor(\"Administrative.Tenants\")]//Added for  DropBox type Lookup\r\n        public Int32? CustomerId\r\n        {\r\n            get { return Fields.CustomerId[this]; }\r\n            set { Fields.CustomerId[this] = value; }\r\n        }\r\n        [DisplayName(\"Customer Name\"), Expression(\"cust.TenantName\"), Width(100), AlignCenter] //Calls upon Name column from Customer Table. \r\n        [LookupEditor(typeof(Script.MatrixCustomerNameLookup))]\r\n        public String CustomerName\r\n        {\r\n            get { return Fields.CustomerName[this]; }\r\n            set { Fields.CustomerName[this] = value; }\r\n        }\r\n\r\nI then added the lookup script to the columns table for customer name but I also hid the CustomerId\r\n\r\n. \r\n        //Client and Location Information\r\n        [Hidden]\r\n        public Int32 CustomerId { get; set; }\r\n        [EditLink, LookupEditor(typeof(Script.MatrixCustomerNameLookup)), QuickFilter]        \r\n        public String CustomerName { get; set; }\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/volkanceylan/Serenity/issues/comments/229762609","html_url":"https://github.com/volkanceylan/Serenity/issues/852#issuecomment-229762609","issue_url":"https://api.github.com/repos/volkanceylan/Serenity/issues/852","id":229762609,"user":{"login":"sayuga","id":16759829,"avatar_url":"https://avatars.githubusercontent.com/u/16759829?v=3","gravatar_id":"","url":"https://api.github.com/users/sayuga","html_url":"https://github.com/sayuga","followers_url":"https://api.github.com/users/sayuga/followers","following_url":"https://api.github.com/users/sayuga/following{/other_user}","gists_url":"https://api.github.com/users/sayuga/gists{/gist_id}","starred_url":"https://api.github.com/users/sayuga/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sayuga/subscriptions","organizations_url":"https://api.github.com/users/sayuga/orgs","repos_url":"https://api.github.com/users/sayuga/repos","events_url":"https://api.github.com/users/sayuga/events{/privacy}","received_events_url":"https://api.github.com/users/sayuga/received_events","type":"User","site_admin":false},"created_at":"2016-06-30T19:24:54Z","updated_at":"2016-06-30T19:24:54Z","body":"I did attempt that. (see sample below) but that just creates the drop down list but there is no dynamic search. I can't figure out why. I believe it has something to do with the transition of the Id field after it has been through the Join but I can't seem to figure it out. I feel I'm overlooking something basic. \r\n \r\n[LookupScript(\"FMCorp.MatrixCustomerName\")]\r\npublic class MatrixCustomerNameLookup : MultiTenantRowLookupScript\r\n{\r\nField fld = MyRow.Fields.CustomerName;\r\nField fldtwo = MyRow.Fields.CustomerId;\r\n\r\n    public MatrixCustomerNameLookup()\r\n    {\r\n        TextField = fld.PropertyName;\r\n        IdField = fldtwo.PropertyName;\r\n    }\r\n\r\n    protected override void PrepareQuery(SqlQuery query)\r\n    {\r\n        query.Distinct(true)\r\n            .Select(fld)\r\n            .Where(\r\n                new Criteria(fld) != \"\" &\r\n                new Criteria(fld).IsNotNull()\r\n                );\r\n        AddTenantFilter(query);\r\n    }\r\n    protected override void ApplyOrder(SqlQuery query) { }\r\n}"}}
{"actor":{"display_login":"sayuga"}}