Relationships and Portals: Data entry changes upstream records! Bad DogCow! Bad!

[Guy_Smith]

Here’s my basic problem (other than my robust two-digit IQ):  When I change data in a Table via a Portal, the data in an upstream Table gets changed as well, which completely messes up the entire Universe as I know it.

Here are the particulars:  I’m developing a solution strictly for a Desktop computer which will be used very intermittently by a max of 6 people.  It will be used to track Air Emission Reduction Credits (ERCs): 

            A company can own many ERC Certificates, but an ERC Certificate can belong to only one company.

An ERC Certificate can have up to five different pollutants on it (NOx, SOx, VOC, CO, and PM10), but each of those pollutants has to be on an ERC Certificate.

The amount of each pollutant on the ERC is initially calculated (by a long and dreary process) and assigned to each pollutant on the ERC Certificate.  There are two “types” of ERC Credits for each pollutant, depending on the geographical area they were generated in.  

Many different Certificates can have the same pollutant(s) listed on them (i.e. Certificate 001 can have 100 lbs of NOx on it and Certificate 002 can have 88 lbs of NOx on it), but each Certificate’s ERC Credits should be tracked separately.

Once the ERCs are initially assigned, they may be used in whole or in part to offset new projects, sold to other companies, transferred, etc.  Upon use, the ERC Certificate is then re-issued with the remaining ERC Credits listed.

Example:  ABC Industrial Company shuts down a boiler to gain ERCs.  After the calculations are done, they receive an ERC Certificate (say, number 111) with, for example, 100 pounds of NOx Credits and 200 pounds of VOC Credits listed on it.  Later, they sell 50 lbs of NOx Credits to another company and also use 85 lbs of VOC credits to start up a new boiler.  Their ERC Certificate would be reissued with the new totals of 50 lbs of NOx Credits and 115 lbs of VOC Credits listed on it.  They can continue using the Credits until they are all gone:  If they use all of the NOx Credits but still have some VOC Credits, the reissued Certificate will only list the active VOC Credits.   They also hold another Certificate (say, number 112) with 75 lbs of NOx Credits on just sitting around gathering dust.  Certificate 112 is completely unaffected by changes to Certificate 111.

With my current solution, I can create the records for the initial credits via a portal from the Certificate TO/layout, but when I try to create a record for the usage transactions TO (again via a portal on the Certificate layout), the data in the initial credits portal changes.  Aaaaarrrrgh!  I’ve tried every combination of relationships and portal setups I can imagine, all to no avail.  I tried to stick a join table in between the Certificate TO and the Pollutant TO to fix the problem, but couldn't figure out to make that work, either.  I have attached a copy of my solution to aid in any troubleshooting you might be able to provide.

Any suggestions/corrections/brilliant ideas would be greatly appreciated.  Thanks in advance for your time and assistance.

Sincerely,

Guy

FMPro Advanced 15, 

Windows 10

ERC Registry v2 Mod.fmp12

[dwdata]

Hi Guy,

I made a few mods to your architecture. Please review attached file.

Let me know if it needs adjusting.

Good luck!

 

ERC Registry v2 Mod_dw.fmp12

Edited July 14, 2016 by dwdata

[Guy_Smith]

Don:  Thanks so much for the enlightenment!  I went through your improvements and found my main problem was solved through your join table - Pretty Cool!  I hadn't even thought of that approach.  I'm not savvy enough to figure out your modification to the Pollutant Abbr value list, but I assume that it uses both the Pollutant ID and the Abbr to tie the usage entries in the bottom portal to a specific pollutant entity.

I think my only remaining stumbling block is that I can't seem to enter new Initial Emissions Reduction Credit Pollutants into the top portal on the Certificate layout, even though the "Allow creation of records in this table via this relationship" box is checked in the Relationship Graph and I can enter data into the Quantity, Source, and FONA fields no problem:  When I click on the Pollutant field, nothing happens.  Unfortunately, without that field being a simple drop-down list that severely restricts what can be entered into the field, there's no telling what's gonna wind up in there!

Once I get a pollutant to show up in the Initial Issue portal, the Usage portal works beautifully!

Thanks again,

Guy

 

[bruceR]

And FWIW, I would do it more like this.

 

ERC Registry v2 ModBFR.fmp12

Edited July 14, 2016 by BruceR
Update Pollutant layout.

[Guy_Smith]

Bruce and Don:  It's more than a little embarrassing to realize that you guys understand my workflow better than I do!   I'll be dissecting your solutions over the next few days/weeks to learn from the experts and I can't thank you enough for your unselfish support of us novices.

For Bruce:  Just like I pestered Don for a bit more help, here's a follow-on call for your assistance:  Once the ERCs are issued and the company starts using them, they usually use them a little at a time (i.e. They start with 500 VOC Credits and use 100 of them one month, 200 the next month and 44 more the next year).  The ERC Balance isn't subtracting the TOTAL usage from the Initial Issue, but rather the last entered usage (In this case it would read 456 instead of the correct 156).  I tried to use a multi-predicate relationship to filter the credits, but just can't seem to get it:  Could I please bug you for a bit more help on this?  I really like the way you locked down the initial entry process - that should guarantee some great consistency.

Thanks again, gentlemen - I'd never have been able to finish this without your support.

 

Best Regards,

Guy

[bruceR]

I'm aware that my version still requires some further mods to calculations etc.

For the moment, I'm leaving that as something for you to figure out.

Edited July 15, 2016 by BruceR

[Guy_Smith]

Sounds good, Bruce!  I'll let my brain cell curl up and take a nap for a bit, then see what I can come up with:  You'd think simple addition wouldn't be too hard to fix. 

Thanks again and enjoy your day!