Subject Re: Data module with a complex index From Akshat Kapoor <akshat.kapoor@kapoorsons.in> Date Fri, 6 Dec 2019 17:28:26 +0530 Newsgroups dbase.getting-started Attachment(s) Printing _ORDER BY clause.pdf On 06-12-2019 15:09, Robbie Nott wrote: > On 06/12/2019 11:23, Mervyn Bick wrote: >> On 05/12/2019 20:13, Robbie Nott wrote: >>> Hi dBase >>> >>> I have a 3 level relationship >>> >>> Show >>> Cat >>> Ring >>> >>> Cat only displays cats in the show >>> Ring only displays the rings for the Show and the Cat >>> >>> Easy with "old fashioned" indexes >>> I create an index in the Ring table concatenating the Show and Cat >>> >>> Wanting to do the same thing in a datamodule ??? >>> >>> Any help appreciated >>> >>> Many thanks as always >> >> I need a bit more information to help me get my head around this. >> >> Firstly, what version of dBASE are you using? >> >> Do you have three separate tables for Cat, Show and Ring? >> >> If so, do you have a unique primary key in each table? >> >> If not, what tables do you have and what fields are in them? >> >> >> Mervyn. >> >> >> >> >> > > Hi Mervyn > Thanks for the reply > dBase 12 > Attempting to learn the data modules. > > 3 tables, Show, cat and ring > Many cats per show, many rings per cat > > Show table has the ShowNo field. > Cat table has ShowNo and CatNo fields. > Ring table has ShowNo and Catno fields which are the >   fields used to filter the Ring records > > Rings don't have a unique number, rather they are linked > by show and cat number. > Existing system creates a concatenated key of > Show["ShowNo"] + Cat["CatNo"] for restricting the ring records. > Ring table has a ShowCat index, and program uses a setRange(). > > Wanting to do a data module for this and not sure how to proceed. > Perhaps a calculated virtual field in the Cat table ? This is just a thought in addition ot Mervyn's reply. A simple question, Will the index order be changed during execution of the form, or you will be using the same index. If you are using the same index throughout then I will suggest use of ORDER BY clause of SQL. This has the advantage of not requiring an .mdx file so fewer chances of corruption / out of date indexes etc. but also suffers from certain drawbacks like rowset could be readonly and you will not be able to change the index order without changing the sql statement. So plan well before proceeding in this direction. Regards Akshat