Help Please! Summarize Portal (see attached)

[deego55]

in hopes that someone can help me!

the business problem: for a task, we can have several funding programs for the same fiscal year. we have a funding portal set up that allows the same fiscal year to be chosen more than once in case there is more than one funding program. dollar amounts and program codes are entered for each row

what we need: two other views of the portal - one that shows a "rollup" to the fiscal year level and one that shows a "rollup" to the program level

I included a stripped down version of the file in hopes someone can help me. if anyone can help i would be most obliged. im stumped. thank you so much. please let me know if anything is unclear.

PROJECT_TRACKING.zip

[deego55]

doh. still stumped. anyone smarter than me figure this out?

i would assume that this has to be scripted, and that is fine too.

OR, is it possible use another window/layout that can utilize sub-summaries for a record?

Edited August 25, 2006 by Guest

[Søren Dyhr]

Alright I've investigated you upload, and reconsidered my upload in this thread:

http://fmforums.com/forum/showtopic.php?tid/178902/

What I use now in my alternative approach is to have a prefab set of records shown in a selfjoin portal plucking primary keys from ValuelistItems( to make multi-criteria relations work their way thu the various key combinations, to grap something to sum.

There are however some freshing issues with the ValueListItems( function and perhaps there can be found some optimizations via GetNthRecord( inside a CF...

But more generally do I have a hunch that, i'm ignoring something more obvious ...because I can't help feeling that this approach is a little clunky ...well it works sort of. What is bothering is that the layout is desired tabbed, so a scripted approach such as:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

...would have to switch tabs and then use a event trigger plugin to fire the Edohshin algorithm.

My feeling is that this task as such is way into spreadsheet realm, and not a database task which is more enclined to utilizing subsummary reports.

--sd

PJTrack.zip

[comment]

How about this way?

BTW, you can trigger scripts on changing tabs without a plugin - see here.

SummarizePortal.fp7.zip

[Søren Dyhr]

Yes it's either you or Ugo, who should pride yourself with this approach:

http://www.kevinfrank.com/download/repeating-lookups.zip

Take a look at the attached image, this must be the way your suggestion should be interpreted. But since you should arrange this data in portal with 10 sequentially organized AWP's ...should some kind of event trigging happen, or would you slice your portals vertically? How would you deal with voids in the listing - causing a sum to be shifted to a wrong posistion?

--sd

[comment]

Ugo is the father - I'm just the uncle that takes the kid to all the fun places...

I think your quesion is another topic. I lean towards using a relationship per column for this, with the keys calculated dynamically in the viewer table (so you can horizontally scroll the years, for example).

BTW, left outer join is currently #1 on my wish list.

[Søren Dyhr]

I think your quesion is another topic

Unfortunately not Michael, in the thread:

http://fmforums.com/forum/showtopic.php?tid/178902/

...came I up with a template, with almost the same kind of functionality as yours. Although I used lookups... Then some days ago David (deego55) contacted me off forum to make my former approach scale to his desires by leading me to his new thread here!

The scalability is the issue of this thread, but your scrolling idea might be fitting the bill?? - at least do I like it! But on the other hand (I'm obviously generalizing here!!!) is usually excell'ish behaviors instead of hardcore relations mechanics people ask for usually ...in a kind limb before you leap attitude - Although most of the problems in reality should have been approached relational.

But what it really is, is a flat learning-curve, where the tool is prejudicially grasped as something else, where the only thing differs it from the spreadsheet is the user interface. I would guess that a combo of a spreadsheet under neath with a user-interface of Facespan, would more effectively solve the lions share of the problems encountered in the threads here.

Novice developers want to get their hands dirty right away. That is understandable. FMP is cool! It is deceptively simple to use and the temptation to start building something right away is irresistible. DON’T DO IT!

This is advice almost every novice developer receives and ignores. And it is what gets them into more trouble than anything else.

A software project is like anything else you construct. It has to be designed first or it will not turn out well. You wouldn’t think of building a house without a plan, why would you consider building something perhaps even more complicated with no plan?

--sd

[comment]

It is different in the sense that one collects the categories from the data, and the other has a pre-defined structure. IOW, if there are no entries for 2005, one will skip the year, and the other will show a zero or empty value for 2005 (hence my wish for a real left outer join, which would really make this kind of thing a lot easier).

excell'ish behaviors instead of hardcore relations mechanics

Not in this case. This is purely for display, so you could call it "excell'ish behaviors in addition to hardcore relations mechanics".

[Søren Dyhr]

Alright you can design your layout by having invisible tabs and have scripts letting you to the right one, by using the new Go To Object or the former way of attempting to write to a cut up repeating field as in Honzas template. This way the script gets executed ahead of the opening of the desired tab.

I have now made a suggestion using Ugo/Michael s appoach to the scripting required to make the cross-tab report happen according to the entered data, while putting less stain on the derived relational stuff I used in my previous template.

I post it here to get feedback as to where the script could be tightend further?? Since the approach depends quite of the swiftness in the processing, however havn't I thrown much more efford than the thought, into making fake tab's as Honza's template suggest. So the data is here burdened by a syncronization issue the switching of tabs would dela with.

--sd

MoreScripted.zip

[deego55]

hi Soren and comment. Thank you both for the helpful advice here. I got the solution to work for the ficsal year and the program, but have not taken into account any of the AWP amounts.

Soren, i have not looked at your latest posted solution yet. I will do so today.

Thanks again for all of the help. Id be completely lost without these forums!

[deego55]

I agree with David Kachel's comment about novice developers. Unfortunately i am the only resource that my small company has available. I often look to these forums for help from more advanced developers like Comment and Soren. Hopefully they will continue to provide me with fantastic help.

Its amazing how easily you can get in over your head as a novice, and i cant thank the posters on this website enough for the help they have provided.

[Søren Dyhr]

Please... it leaves a little me a little uncertain if the advices I provided, got you any further? We or at least I could have grasped out for the wrong straw, to cling on to? Tell us if it's leading you anywhere.

Regarding the scarceness of resources, isn't it hardly the lack of technical skill that burdens smaller companies, but instead their sole focus on technical skills that is their Achilles heel.

Venture capital hardly ever cares for technicalities, the focus should be on cashflows and markeds ...otherwise would Jazz music be the most promoted type of music, and twangy 3 chord C&W be doomed oblivion.

--sd

[deego55]

hi soren- yes, you have helped me immensely. your morescripted.zip file is VERY close to what i need. brilliant solution! it appears that this summarizes to the 'fund' level. is there a way to modify this so that it goes even further to the fiscal year level? so if there are two funds with the same fiscal year they will be summed together in the second portal

thank you again.

[Søren Dyhr]

Pretty easy get rid of the fund as criteria in the RG....

But I think I would make that in an extra tab, still chopped up in AWP's via the script if nessersary.

--sd

[deego55]

Excellent! Thanks again SD!

Thank you so much for your help regarding this issue! Your final solution is brilliant. I would have never come close to figuring this out without your help.

One more question: I got the solution working in my file, except that my AWP sums are one number off because I have a AWP 00 field. For instance when i add an amount for AWP 01, it shows up in the AWP 00 Sum field. Then if I add an AWP amount for 02, it shows up in AWP 01 sum field, etc.

I am looking to see where the script defines where to place the values, but thought you may have a quick answer. Other than that, this is EXACTLY what i was looking for!!

Thank you again for helping a novice.

Regards

Edited September 7, 2006 by Guest

[deego55]

Looks like i was able to solve this by changing the repetition calculation on the array variable to:

1 + Abs ( zTask_Funding::zAWP )

Do you think there will be any issues with this?

[Søren Dyhr]

No we're only typecasting here nothing else! Just go a head!

--sd

[deego55]

Soren-

Remember helping me with this AWP typecasting solution? It works brilliantly, unless the first record in the portal does not have a value that it is looking for, then it ignores the remaining related records. So if the first related record has a blank AWP field, and the second record has a value, the user still gets the "no values found" message. do you think this can be fixed?

Thanks again!

[Søren Dyhr]

In order to make a multicriteria relation work, must all keyfields have values, so I would solve it like shown in the hack of the outer script above.

--sd

[deego55]

thank you Soren! i will try this tomorrow. youre a great help.

[Søren Dyhr]

I thought of another thing as well, make the record creation stuff default values, the user then have to alter.

One important point here is that I would autoenter 0 in the value field....

--sd