MasterDetail - differentiate between params from Master and Input params lazarus / 16-Databases ---------------------------------------------------------------------- JZS · 2014-03-30 15:01 Hi All, I have got stuck a bit here. I am using a Master-Detail structure, where the Detail will be looking for an ID from the Master. But there is another Input parameter that it's value will be introduced at run time (Date). So the Master offers the Store ID's and the Detail will show data based on the StoreID found but within the respective Date condition. As follows: Master: Select * from Stores; Detail: Select ItemID, Date, Qty, StoreID from StoredItems where StoreID = :StoreID and Date <= :Date How can I pass the Date without the Detail look for it from the Master? I tried marking the input parameter as Input but, no use. What am I missing here? Thanks. PS: The names/structure used are just samples. There is no mismatch and proper naming & referencing is considered. I am using Laz1.2 fpc2.6.2 on Win32 with MySQL no 3rd party comps. ---------------------------------------------------------------------- exdatis · 2014-03-31 06:15 In my opinion, you need to (more) carefully project the database. See: http://en.wikipedia.org/wiki/Database_normalization ---------------------------------------------------------------------- exdatis · 2014-03-31 06:20 http://wiki.lazarus.freepascal.org/TSqlite3_Master_Detail_Example ---------------------------------------------------------------------- JZS · 2014-03-31 07:44 Quote from: exdatis on March 31, 2014, 08:15:47 amIn my opinion, you need to (more) carefully project the database. See: http://en.wikipedia.org/wiki/Database_normalization Thanks for your reply ExDatis. Let me assure you, I have considered that from the beginning and everything was done after deep analysis and very carefully designed. My question applies to any Master-Detail structure no matter how it's designed. I have come to need this while building up reports, not while handling and working with the data entering or projection. You will hit this if you tried to do a report for the Demo from the link you provided "TSqlite3_Master_Detail_Example". There is: cust (ID, CustName, Addr, Suburb, postCode, custState) sales (itemNum, ID, custID, saleDate, shipDate, item, price) Right? Consider this, preparing a report for "Sales" (as a Detail), but we need to categorize sales by "Customers" (as Master) on given date (or range of dates). Such as      Customer x:             Item1     Date1    price             Item2     Date2    Price      Customer y:             Item1     Date3    price             Item2     Date4    Price So in the report, Customers will be Master and Sales will be Detail and linking them will be by Select * from Sales Where sales.custID = :ID  /* :ID stands for cust.ID from Customer Table*/ and sales.saleDate between :Date1 and :Date4  /* :Date1 and :Date4 will be specified by the user at runtime */ :Date1 and :Date4 are to be supplied before opening the Detail Table which should not be looked up from the Master Table. I can easily solve this by passing the dates and building the Query as string, after knowing the dates range, but I am trying to clarify if there is a possibility to do that with params. Does it make any sense? Thanks ---------------------------------------------------------------------- exdatis · 2014-03-31 07:53 I'm so sorry but today I haven't enough time, if you want I can send you a small application, like example. I suppose that it's will be useful. Best regards ---------------------------------------------------------------------- JZS · 2014-03-31 08:06 Quote from: exdatis on March 31, 2014, 09:53:57 amI'm so sorry but today I haven't enough time. No one does  No rush. Thank you. ---------------------------------------------------------------------- BigChimp · 2014-03-31 08:57 Quote from: JZS on March 30, 2014, 05:01:02 pmSelect ItemID, Date, Qty, StoreID from StoredItems where StoreID = :StoreID and Date <= :Date How can I pass the Date without the Detail look for it from the Master? I tried marking the input parameter as Input but, no use. Looking at the documentation http://www.freepascal.org/docs-html/fcl/sqldb/usingparams.html perhaps try rewriting this as Code: [Select]Select ItemID, Date, Qty, StoreID from StoredItems where StoreID = :StoreID and Date <= :MyOwnDateParam i.e. the parameter for the date field should not be named the same as a field. ---------------------------------------------------------------------- JZS · 2014-03-31 09:28 Thank you BigChimp. That was just an improvised sample based on ExDatis' link. In the actual query no matter what the name is, the response I always get is: MasterQuery : Field not found : "MyOwnDateParam" Param Fetching will always be done by looking for those params from the MasterDB rather than excluding the params that have got values as input before executing the query. ---------------------------------------------------------------------- BigChimp · 2014-03-31 09:34 Sorry, I think I missed the reporting part. Are you talking about reports with LazReport? ---------------------------------------------------------------------- JZS · 2014-03-31 09:39 I am building the report with Fortes Report ---------------------------------------------------------------------- BigChimp · 2014-03-31 09:51 Then I definitely have no idea... ---------------------------------------------------------------------- JZS · 2014-03-31 09:59 It's not about reporting, BigChimp. The point is, if that is how fetching the params between Master-Detail is done, then I guess there is something to be fixed. There should be flexibility of allowing params at the Detail to be provided values by users instead of dedicating every single defined param to be fetched from the Master. ---------------------------------------------------------------------- BigChimp · 2014-03-31 10:17 QuoteThere should be flexibility of allowing params at the Detail to be provided values by users instead of dedicating every single defined param to be fetched from the Master. That is indeed the aim of the sqldb code AFAIU. If that doesn't work, it's a bug. However, I have no idea how FortesReport deals with queries (passing parameters etc) so I can't comment on that. If you can provide a test case (FPC, not Lazarus) http://wiki.lazarus.freepascal.org/Database_bug_reporting#FreePascal that demonstrates the behaviour, that will help. ---------------------------------------------------------------------- JZS · 2014-03-31 13:00 Thanks BigChimp, here is the conclusion: There is a bug. When the Master-Detail is configured at design time then the Detail params will be looked up, in order to be fetched from the Master DB, irrespective of whether these are going to be input params or not. In this case, all params will be treated as Detail-Filtering params and their values will be requested from the Master table, once the Master DB is opened (I mean Master DB, even it does not wait to open the Detail to evaluate). And hence the error message pop up saying [Master Table : Field not found : "TheInputParam"]. Upon testing in Console there was no issue. Even if the query was populated in GUI at run-time, there will be no issue. The issue only happens when adding the query in the SQL property of the TSQLQuery (Detail Query) at design time. ---------------------------------------------------------------------- BigChimp · 2014-03-31 13:22 Thanks for digging into this. Could you report this as a Lazarus bug in the bugtracker with these details & preferably a sample project? Thanks. ---------------------------------------------------------------------- JZS · 2014-03-31 14:23 Reported. http://mantis.freepascal.org/view.php?id=25955 ---------------------------------------------------------------------- eara · 2014-04-10 23:19 a quick solution that you may have till they fix this, is to declare a dummy calculated field in master table with the same name, that will match the parameter from detail query... ---------------------------------------------------------------------- JZS · 2014-04-12 13:54 Quote from: eara on April 11, 2014, 01:19:39 ama quick solution that you may have till they fix this, is to declare a dummy calculated field in master table with the same name, that will match the parameter from detail query... Nice trick. Although calc field requires all the field to be populated first. I avoid that, as a work around, I introduced the dummy field in the SQL Query, and it worked: "Select *, 0 AS InPutParam from DBName.TableName;" Many thanks. That helped alot. ---------------------------------------------------------------------- eara · 2014-04-13 15:24 this is also a nice workaround, but has it's own (+) and (-) The (-) that i see there: 1) you must rewrite the queries 2) it adds the overhead to db-connection (dummy 0 from db for every record) but if you are happy with it ... by the way why do you care for calc field population??? Just add a dummy calc field, which you will never calc. Just for foolling the detail datasource that it exists. Every time you run the detail query you pass your own values for this field... so no overhead i think. of course, the best is to have the fix, to avoid all this...