6 Replies Latest reply on Mar 11, 2014 3:50 AM by erolst

# Calculation field or script at portal?

Hello Everybody,

This is my first post and I would like ask you guys to help me find solution with portal and calculation field in it.

At begining I really sorry for my poor english - I'm doing my best and still learning.

What I have:

- portal with building selector

- when building is selected, portal shows floor number, room number and room area at the building

- last field assigned to that portal - room area * (n+1) - is calculation field where count this formula:

"Let ( [\$var1 = \$var1 + 1; RoomAreaCalc = RoomArea * \$var1; \$var1 = \$var1 + 1]; RoomAreaCalc )"

- this field increments room area by "n+1" number (and increment stored values again when building have been choosen again)

What I would achieve:

- calculation should be done once when I again select same building (not increment stored values again)

- multiplication should be done from 1 not from 0 (as you see I tried to set variable to 1 but first calculation shows that don't work properly)

- is this can be implement as script? I really thinking about using an IF/LOOP/... formula with script parametr but at this moment I don't understand how script parametr is working and how script can be implemented with portal

- if I can clean variables each time when building have been selected than part of my idea can be done (searching at FM forums shows me that can't be done when project is loaded except when project have been closed and loaded again then all variable values will be restarted)

I really appreciate any suggestion that can solve this problem.

Best regards,

Kamil

PS. Attachment contain main whole and very first project and it's a little bit messy - you should interest buildings_portal layout.

• ###### 1. Re: Calculation field or script at portal?

Can you explain a little more why you want to have this incrementing field?  What purpose does it serve?

• ###### 2. Re: Calculation field or script at portal?

wimdecorte wrote:

Can you explain a little more why you want to have this incrementing field?  What purpose does it serve?

Sure!

This exercise basicly are serve for understanding how works calculation field (and calculation in whole FM envirement) and solve any problems that I encountered - nothing less, nothing more. At begginign this calculation - as I said above - I would like to implement as a script, doing loop/if to make this calculation but I have no idea how.

Maybe I choose wrong place to ask begginer questions? If yes, please move this thread to more suitabe place at this forum.

Thanks for any explanation!

• ###### 3. Re: Calculation field or script at portal?

Kamil –

I gather this is not a purely theoretical exercise, but has a practical application – namely, to find the number of rooms and their combined area.

What you tried is probably a recursive function – which needs to call itself, and since that doesn't happen in your calculation, well …

The recommended way to write recursive functions in FM is via a Custom Function; using \$vars in calc fields to implement this can be a bit hairy and is probably not the best introduction topic into “how works calculation field (and calculation in whole FM envirement) ”. Maybe start a bit slower with If(), Case(), Count() and friends …

Having said that, no recursion is necessary; here are a number of methods to get at your results (which (the methods) themselves should provide some insight into the workings of FM):

– create a self-join between Rooms, matching buildingID and floorNumber/ID, and count/sum the related records.

– do the same via ExecuteSQL

– create/maintain a Building|Floor table and count up the related Rooms record (same as the first method, but properly normalized; scripting required)

– create a report layout based on Rooms and summarize your fields using sub-summary parts and summary fields (scripting required)

– create summary fields in Rooms and use a combination of relationship sorting and Conditional Formatting (FM12) / Calculated Hide (FM 13) to display summarized results in the portal.

– if you want scripting (without report/join table), walk the rows of the sorted portal in a loop, create summaries from the values, then write them into a field

– if you really want to try your hand at recursion, pull in lists of values from the relationship the portal is based on, and process them floor-/room-wise (where the relationship needs to be sorted by floors); basically the same method as the previous one, but you don't need an actual portal. **

Which of those method is the “right” one (or ones) depends on the intended workflow, your reporting/UI needs and (last but not least) your skill level. It might be a nice exercise to try them all …

Again having said that, here's the calculation you probably wanted to write (re-create at your own risk):

• ###### 4. Re: Calculation field or script at portal?

Thanks for replay!

I "bite" your propositions and write few more words tomorrow. Thanks for your effort and attached example!

Cheers

• ###### 5. Re: Calculation field or script at portal?

Here I'm back...

After quick review - as you said - Custom Function at this moment this is "a little bit" hard core for me, but I'll fallow your words and try all possibilities using "If(), Case(), Count() and friends".

Another question concerns on this sentence

– create/maintain a Building|Floor table and count up the related Rooms record (same as the first method, but properly normalized; scripting required)

If I have in my project both tables Rooms and Floors one-to-many relationship I think there are properly normalized, am I right?  Going this way Joint Table is recomended when relationship is many-to-many, so your proposition it's result of FM specyfic or something else?

Step by step I'll try test all proposed variants...

Thanks for all hints!

Regards

Kamil

• ###### 6. Re: Calculation field or script at portal?

lokaty wrote:

After quick review - as you said - Custom Function at this moment this is "a little bit" hard core for me, but I'll fallow your words and try all possibilities using "If(), Case(), Count() and friends".

Actually, what you see in that file is not a Custom Function, but a normal, albeit recursive function; but I get your drift …

lokaty wrote:

If I have in my project both tables Rooms and Floors one-to-many relationship I think there are properly normalized, am I right?  Going this way Joint Table is recomended when relationship is many-to-many, so your proposition it's result of FM specyfic or something else?

I wasn't talking about your structure as such, but rather where to properly store the data that you need to have a 'live' floor statistic. Neither Rooms nor Buildings are the correct place to keep these data in a normalized structure.

So, if you actually have a Floors table (instead of having none and just dropping a floor number into a room), where each Floor record has a primary floorID and a foreign buildingID, and each Room record has a foreign floorID …

… then this Floors table should be the proper place keep the data (and is more or less the table I suggested). Use calculation fields with Count()/Sum() for the related rooms (or, if you need to speed up the display, use indexed numbers fields that you maintain using scripts).