BRAVO to you, Mark! You are a genius! I knew it didn't make sense that you can't figure this one out. You were with me right in the beginning over 20 years ago (if you remember) when I had those challenging questions. But I do appreciate the help I got from the brilliant minds of Don and Brian. I saved their advice for other occasions.
The reason it didn't work was simply because I ONLY set up one index - from the source to the Parent record. With your advice, I added an ADDITIONAL index in the source to the Child record, the Data Link. Now the cascade update and delete feature works like a charm for both, the Parents and the Data Link records. Thanks a million for explaining your idea over and over and not giving up. All the best Malkie > Do not cascade through the data link cascade through a "panel link." > data links update 1 parent record only. panel links update all > children records. You need to make a new panel link in the MI database > and follow my directions. I tested this out prior to mailing you the > answer and it worked for me. You are doing something wrong. > > Best Regards, > > Mark > > > > > From: [email protected] > > To: [email protected] > > Date: Tue, 12 Oct 2010 03:17:06 +0200 > > Subject: Re: [Dataperf] A Challenging Problem > > > > > I think I understand now. Calculated fields will work. However to > > > answer your question. In your specific case you would create an > > > index in the relative field which includes the fields you wish to > > > be updated as the first fields in the index followed by fields > > > required to keep the index unique. Then in the MI panel create a > > > panel link to the relative panel use this new index and mark the > > > fields in the Key fields list in the same orde as you placed in > > > the index created in the relative panel including only the fields > > > needing updating. Turn the cascade update on then hide the panel > > > link. Now when you update any of the address fields in MI it will > > > automatically update all related panels through the link created. > > > > > > Best Regards, > > > > > > Mark > > > > Mark, that's what I thought too and it is exactly how I created that > > Relative panel until my client made me aware that the changes are > > not being updated in there. That's when I decided to approach the > > experts of the DP Discussion Group. Apparently, the cascade update > > doesn't work for Data Link records. Only the parent link get > > updated, i.e. John Doe's data. > > > > > > Brian, funny that you mention Keep A Total. That's how I created > > this panel initially totalling it to the MI but it didn't help much > > with the updating to the Relative panel. > > > > Don, your idea of a calculated field with a formula on all the > > fields is exactly what I need as it seems to be doing the job > > properly. Thanks. I cannot make the Data Link field into a > > calculated field though because, as Brian mentioned, there has to be > > a real field. So this real field contains the ID number. > > > > However, there's one problem with your idea, Don. If the record in > > the source panel gets deleted, the Data Link record stays on because > > the ID number is not 0. How can I get it to automatically delete the > > record if the source record is not found and all the calculated > > fields are blank? This is not a major problem. I can have them > > delete all those records with blank calculated fields at once after > > a while, but that's not the proper way. Is there a way for it to > > automatically get deleted cascade-style? > > > > I will also hold onto the KAT I initially created to keep count of > > the amount of Data Link records each person has but will add to the > > formula an extra IF one of the calculated fields is blank, to make > > the count "0", so that the source panel will calculate properly if a > > record in the source panel gets deleted. The only problem is now how > > to delete those blank records in the Relative panel? > > > > Thank you all for your wonderful help. What a bunch of great guys > > you are : ) > > > > Malkie > > > > > Hi Malkie, > > > > > > Don is right about using Calculated Fields. However I suspect that > > > you want the same field from both panels to be synchronised, which > > > obviously means that one has to be a real field and not a > > > calculated field, so unless you are prepared to have a parallel > > > mirrored calculated field then this might be a problem. > > > > > > Formulas in calculated fields are "pushed" whereas formulas in > > > real fields are "pulled" meaning that there has to be an edit > > > action on the target record to trigger the pull from the source. > > > > > > The keep a total is one way of triggering an edit action in a > > > different record, and a technique that can be used is to have a 0 > > > - 1 switch in a record, with a formula on a real field that > > > updates each time the record is edited, much like the formula IF > > > field=1 then 0 else if field=0 then 1 endif, but that can be > > > unpredictable as I think each edit on the first panel would create > > > a wave of triggers, plus this works through a many : 1 data link > > > and not a 1 : Many panellink. > > > > > > The Cascade on and off on a link is to control when there is a > > > change in the Primary and Foreign keys across the link. So for > > > example if you have the two records linked by an ID and the ID in > > > one changes the Cascade On "pushes" the changes to the related > > > records ID. This is so that a change in a Primary Key of a parent > > > record doesn't orphan the child records. > > > > > > There is perhaps a possibility here to create a Panel link using a > > > two field index for each field you want updated, eg ID + address. > > > ID + phone etc and the panel link based on those indexes linking > > > both fields in both panels so that the link thinks that both > > > fields make up a key field and then my push propagate the changes. > > > I haven't tried it just now, in a bit of a rush, however although > > > it sounds like it could work there are issues about the "parent" > > > record getting a blank. I would try this. Create a second field > > > the same as the ID field (lets called it ID2 which has a data link > > > so it can look up the "parent". Create a Panel link from the > > > "parent" using the ID + Phone fields as the link index, linking > > > recursively to the "child" using the ID2 and Phone fields as the > > > target, and if this works then similarly repeat this for each > > > field you need update. Now the issue that worries me is that even > > > the parent record in relation to this link is also a child, and so > > > will be looking to be a target, and so data you put in the > > > "parent" panel might get zapped, but perhaps you could get around > > > this with an Exception List on the index. You will also need > > > datalinks from the "child" to the "parent" so that when the child > > > record is created that a formula can pull data from the "parent" > > > into the respective update fields. Oh my oh my, there are so many > > > things to go wrong. > > > > > > Synchronous multi-record manipulations is perhaps an area where DP > > > by itself is not quite up to the job; the Keep a Total and the > > > Panel Link Cascade do a lot but it really needs something more. So > > > another alternative I think might be useful looking at is DPMouse > > > and/or Shell, I have never used, but I recollect reading about it > > > being able to run reports or macros or something. A report is the > > > best way to handle this problem, but with DP alone there is > > > nothing to trigger this running except a user selecting it and > > > running. > > > > > > This brings me back to my favourite hobby horse: Web Enabling, > > > even on a local network, or even on a local machine. With Web > > > Enabled DP you only ever interact with the data via reports. So > > > all edits, creates, deletes are done from a script importing your > > > information to work with, and then running a report to do you > > > dirty work - you do not directly interact with a Panel except for > > > designing. This means that after every update you could run a > > > second report (or part of the main report) to manage your > > > multi-record updates or any other tasks that might be necessary. > > > > > > Good luck > > > Brian > > > > > > > > > Malkie - the format for a calculated field is simple ::C. The > > > cursor doesn't stop at them, you can't index with them but they > > > simply look at the formula you put in the field and calculate it. > > > For the formula you would simply go through the datalink to the > > > source panel and pull the field you want - phone number, address, > > > whatever. Does that make sense to you? > > > > > > Don > > > > > > > > > From: [email protected] > > > > To: [email protected] > > > > Date: Mon, 11 Oct 2010 20:26:17 +0200 > > > > Subject: Re: [Dataperf] A Challenging Problem > > > > > > > > > If I understand, I think the answer you need is calculated > > > > > fields. > > > > > > > > > > Your relatives panel has to have a key to the records in the > > > > > Master Information Panel. Then it apparently has fields for > > > > > the other information for each relative selected. If those > > > > > fields were calculated fields, they would always match the > > > > > information desired in the Master Information Panel. > > > > > > > > Calculated fields is indeed a nice idea which I actually tried > > > > doing but somehow it didn't work, perhaps because I didn't set > > > > it up properly. Can you please help me with it? > > > > > > > > Malkie > > > > > > > > > > FWIW, that also avoids bloating the data base with repetitions > > > > > of addresses and phone numbers. > > > > > > > > > > Don Codling > > > > > 900 Old Sackville Road > > > > > Lower Sackville, NS B4E 1R1 > > > > > > > > > > On 11/10/2010 8:14 AM, [email protected] wrote: > > > > > > Hi Brian and Mark; > > > > > > > > > > > > Thank you so much for responding. Brian, I don't think it's > > > > > > a template or a formula I would need. All I want is that > > > > > > records in a Data Link of a panel linked to the source panel > > > > > > get updated automatically when the source information > > > > > > changes. As simple as that. > > > > > > > > > > > > There is only one source of address information and that's > > > > > > in the Master Information (MI). The MI has one Panel Link, > > > > > > let's call it, Relatives. The Relatives panel includes a > > > > > > Data Link targeting to the MI from where it selects any > > > > > > number of records. My question is how to get those records > > > > > > that were selected via the Data Link (from the MI) to be > > > > > > automatically updated? Here's an example: > > > > > > > > > > > > I'm in Master Information and go to John Doe. I click on his > > > > > > Panel Link which brings me to Relatives. Now I'm in the > > > > > > Relatives panel and go to the Data Link and select records > > > > > > (all of which are taken from the MI). I select Ann and her > > > > > > address and phone come along and automatically get entered > > > > > > in the appropriate fields created in the Relatives panel. > > > > > > Then I select a new record, namely David, and all his data > > > > > > comes along and are automatically entered in the Relatives > > > > > > panel fields. I do the same for Morris and Betty. > > > > > > > > > > > > My question is if David's address has changed or if Betty's > > > > > > phone number has changed in the main panel (the Master > > > > > > Information) how do they automatically get updated in John > > > > > > Doe's Relatives panel? The Cascade On on the Panel Link in > > > > > > the MI only works for any update on John Doe himself and not > > > > > > for any of his Data Link records. > > > > > > > > > > > > I hope it's clear now. > > > > > > Malkie > > > > > > > > > > > >> Hi Malkie > > > > > >> > > > > > >> I am a little lost with what you are trying to do, however > > > > > >> in response to your reply to Don, there is a very flexible > > > > > >> thing you can do with DP which would spin the head of a > > > > > >> programmer for a modern database, in that you can have > > > > > >> multiple panel links (or datalinks) on a panel and then use > > > > > >> formula logic to choose which link you will use to retrieve > > > > > >> data. It means a Foreign Key can variably relate to > > > > > >> different panel's or even the same panels Primary Keys. The > > > > > >> auto-number recursive link uses this. > > > > > >> > > > > > >> I also used it once to overcome a limitation I had created, > > > > > >> (due to some earlier poor planning) where I later needed to > > > > > >> consolidate invoicing information based on work being > > > > > >> produced in multiple independent panels. > > > > > >> > > > > > >> I am not sure of the recursive nature of your data. Is it > > > > > >> so you can pull back contact information using another > > > > > >> record as a template, eg for family members with the same > > > > > >> address and phone number? If so you do not need the Panel > > > > > >> Link but can do it with a DataLink. For example if you were > > > > > >> wishing to create a copy of the address from another person > > > > > >> in the same table, you would create a field who's format > > > > > >> matched the data format of your Primary Key. On this field > > > > > >> you would place a recursive DataLink, to bring the ID of > > > > > >> the template to copy from back to the record you are > > > > > >> working on. In the fields that you wish to copy the > > > > > >> template data, create a Formula set to trigger on any > > > > > >> change. You can choose whether you want these to update > > > > > >> whenever the parent template changes, or to keep existing > > > > > >> data, depending on whether whole families are more likely > > > > > >> to move addresses or children leave the next Eg to keep > > > > > >> existing data or to allow overwriting of data the formula > > > > > >> in say P1F5:IF len(P1F5) THEN P1F5 ELSE P1F12P1F5 ENDIF > > > > > >> where P1F12 is the template_to_copy field with a DataLink > > > > > >> back to the Primary Key in P1F1 > > > > > >> > > > > > >> But perhaps you want something entirely different. > > > > > >> > > > > > >> Brian > > > > > >> > > > > > >> > > > > > >> How many database panels are involved? In a normalized > > > > > >> database with multiple panels there should be only one > > > > > >> source of "address information" which when changed is the > > > > > >> only place necessary to change. Perhaps I misunderstand the > > > > > >> question. > > > > > >> > > > > > >> Best Regards, > > > > > >> > > > > > >> Mark > > > > > >> > > > > > >> > > > > > >> > > > > > >>> From: [email protected] > > > > > >>> To: [email protected] > > > > > >>> Date: Sun, 10 Oct 2010 20:09:36 +0200 > > > > > >>> Subject: Re: [Dataperf] A Challenging Problem > > > > > >>> > > > > > >>>> Create a panel link (I call it a "recursive panel link > > > > > >>>> for auto data changes"), using fields needing changing in > > > > > >>>> the index for the link, with cascade on then hide it. > > > > > >>>> This should give you the functionality needed. > > > > > >>> Hi Mark; > > > > > >>> > > > > > >>> I'm trying to understand your idea. Are you suggesting to > > > > > >>> create a panel link from the Master Information or from > > > > > >>> the 2nd panel, the one that's linked to the MI and has a > > > > > >>> Data Link to the MI? And then what? What records should > > > > > >>> that new panel link include? Will the Data Link get the > > > > > >>> records from this new panel? The MI has cascade on too. > > > > > >>> What will this new panel accomplish? > > > > > >>> > > > > > >>>> Best Regards, > > > > > >>>> > > > > > >>>> Mark > > > > > >>>> > > > > > >>>> > > > > > >>>> > > > > > >>>>> From: [email protected] > > > > > >>>>> To: [email protected] > > > > > >>>>> Date: Sun, 10 Oct 2010 12:39:48 +0200 > > > > > >>>>> Subject: [Dataperf] A Challenging Problem > > > > > >>>>> > > > > > >>>>> Hello to all those who love a challenge; > > > > > >>>>> > > > > > >>>>> I've been doing DP databases for tens of years but never > > > > > >>>>> came up with the following: > > > > > >>>>> > > > > > >>>>> In this database there is the Master Information which > > > > > >>>>> includes thousands of names, addresses, phone numbers, > > > > > >>>>> etc., as well as a Panel Link targeted to the same > > > > > >>>>> panel. In other words, let's say I would go to John Doe, > > > > > >>>>> press on the Panel Link and select names, addresses and > > > > > >>>>> phone number from the Master Information via a Data > > > > > >>>>> Link. This works nicely. > > > > > >>>>> > > > > > >>>>> My question is if any of the people that were selected > > > > > >>>>> in John Doe's Data Link has an address or phone number > > > > > >>>>> change, how does it automatically change in John Doe's > > > > > >>>>> panel if there's no Cascade On/Off option with a Data > > > > > >>>>> Link? > > > > > >>>>> > > > > > >>>>> I thought I'd be able to solve it with a 'window' but > > > > > >>>>> apparently Data Links do not have windows. > > > > > >>>>> > > > > > >>>>> Anybody out there with a brilliant idea how to select > > > > > >>>>> the information from the Master Information and have it > > > > > >>>>> automatically updated? > > > > > >>>>> > > > > > >>>>> Malkie > > > > > >>>>> _______________________________________________ > > > > > >>>>> Dataperf mailing list > > > > > >>>>> [email protected] > > > > > >>>>> http://lists.dataperfect.nl/mailman/listinfo/dataperf > > > > > >>> > > > > > >>> _______________________________________________ > > > > > >>> Dataperf mailing list > > > > > >>> [email protected] > > > > > >>> http://lists.dataperfect.nl/mailman/listinfo/dataperf > > > > > > > > > > > > _______________________________________________ > > > > > > Dataperf mailing list > > > > > > [email protected] > > > > > > http://lists.dataperfect.nl/mailman/listinfo/dataperf > > > > > > > > > > > _______________________________________________ > > > > > Dataperf mailing list > > > > > [email protected] > > > > > http://lists.dataperfect.nl/mailman/listinfo/dataperf > > > > > > > > > > > > _______________________________________________ > > > > Dataperf mailing list > > > > [email protected] > > > > http://lists.dataperfect.nl/mailman/listinfo/dataperf > > > > > > > > > _______________________________________________ > > Dataperf mailing list > > [email protected] > > http://lists.dataperfect.nl/mailman/listinfo/dataperf > _______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf
