Also in my answer I spelled relative field and I meant relation panel! see below
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 I meant "relation panel" 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
