I’m not entirely sure how you have this structured, so this is a guess, Malkie,
but assume P2F3 is the identifying number for the record you want to show in the relatives panel, and P2F4 is the calculated field for the address, copying an address from P1F5; P2F6 is the link to P1. You might try a formula in P2F3: If P2F6P1F5 > "" then P2F3 else 0 endif The aim is to look through the link to the particular relative's record. If it exists as evidenced by an address existing, then the identifying number remains unchanged. If the record has been deleted, then the identifying number reverts to 0 and your calculated fields should become blanks. The address field may not be your best choice - you need a field that is also populated if that record exists. Don On 11/10/2010 10:17 PM, [email protected] wrote: >> 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
