Portal calculation mismatch

[Walter-MainFrame]

Hi

I Have some dificulty trying to understand on my file why its not possible to related 2 different tables

and do the calculations on the other table

As you can see on my file attached i have a very simple file with 2 layouts only

1 Is the input layout

1 Is the lines layout

And Im trying to get the result board on the input layout to give me the result of the other two inclued layouts

but for some reason i only get part of the result on the first line off the Resul Board

If anybody can give some help

It would be a great help

Thank you

P.s. Account : Admin

Computer Shop.fp7.zip

[Fitch]

No file is attached. Be sure to zip it before uploading.

[Dr.Gopala krishnam raju AMBATI]

:Ask: ur programming is excellent but didnt get what your asking

[Fitch]

Walter, the fields on your portal aren't related in a way that makes sense. It's been a long day, I'll see if I have time this week to look at it, or maybe someone else will chime in.

[Walter-MainFrame]

Well i will try to explain better

I have one input layout were the user can change the selection year for board 1 and for board 2

The third board in the input layout will work as a dashboard and will show the results off comparing board1 values with board2 values

Example

As you can see in record n1 - Apple computer

Year 2010 Board 1

Year 2011 Board 2

In the Result board

Should be

Family

Acessories its duplicated its wrong only want one line with acessories

Desktop its duplicated its wrong only want one line with desktop (and so on)

Acessories ---Total of 1semester is correct 25.769,97 -- Total of first semester board 2 is correct 29.769.97 is correct - Result off semester is wrong should be one minus other= 4.488

Desktop -----Total of 1semester is wrong it should be 107,783,75 --- Total of first semester board 2 is wrong should be 132,982,70 -- Result off semester is wrong should be one minus other= 25,198.95

And so on

I hope everybody understand it better now

[Walter-MainFrame]

Please Help !!!!

[Daniel Shanahan]

I see two questions here:

1. How do have only one instance of a Family in the Result Board?

2. How to get the values correct in the Result Board?

Here is one way to approach this -

Part One: Get one instance of Family in the Result Board.

1. Create table: Family_Table

2. Create field: Family_Table::Family

This is a primary key.

3. Create field Clients_Table::Family_g

Give this field global scope.

Populate the field with any text (e.g. "Family")

4. Create a cartesian relationship:

Clients_Table::Family_g x Family_Table::Family

Part Two: Get the correct calculated data to display in the Result Board.

1. Create following fields:

Family_Table::First_Sem_Board1 (Number)

Family_Table::First_Sem_Board2 (Number)

Family_Table::Result_First_Sem (Calculation: First_Sem_Board1 - First_Sem_Board2)

2. Create script ("update family table" in this example)

3. Set portal fields - Comsumption_Table::JAN, Comsumption_Table::FEB, etc. - in both Board 1 and Board 2 portals to run onObjectExit and pass the parameter of the Comsumption_Table::Family and Comsumption_Table 2::Family for each respective portal.

Computer Shop.fp7.zip

[Walter-MainFrame]

Well This solution would work, But I Really think since all the information is already in, it must be a easier way to do this,

[Fitch]

There really isn't a simple way to do this. What you're trying to create is essentially a cross-tab report in a portal. It's going to require what Daniel suggested or something similar.

Perhaps you could settle for putting your top two portals side by side, instead of the third portal.

[Walter-MainFrame]

Ok Fitch

Portal one and portal two side by side but stil how would i present the result ?

In Portal 2 for example ?

[Fitch]

Something like the attached?

Computer Shop2.zip

[trevix]

You need a few adjustment, but I think this could work, if I have understood...

Trevix

Computer Shop.fp7.zip

[Walter-MainFrame]

OK Trevix

Brilliant !!!! TouchDown i just had the idea it could be done,

Let me try it out so i can test the logic off other calculations on it

Great Job....

Thanks

Walter

A thank you for your effort Daniel and Fitch

[Walter-MainFrame]

Just made some test it seems to work perfect....

I am trying just to fix a litle thing when a Family dosent show on year off Board 1 but does exist on year off board 2 it dosnt show up in the result board,

[trevix]

Sorry...did not think about that.

You should explore having another file with unique families (may be with prices or info...) to be shown on the portal.

See how i have been helped in this topic, it could help.

http://fmforums.com/forum/topic/83525-charting-one-table-data-in-a-different-table-layout-how/page__gopid__387043#entry387043

Download my last file only and see how is made.

Trevix

[Fitch]

See attached file. I changed the bottom portal's relationship back to Vat Number only, and used a portal filter to remove the duplicates.

Computer Shop4.zip

[Walter-MainFrame]

Fitch I have just looked at it but i have the same problem

When you Add a New Record " In comsuption layout with some data and with the a new year Imagine 2009

And you go back you will see it does not work

What happens is the filter is only taking duplicates out, but its showing all the familes for that vat number

When you select the same year in the two boards for example board1 2010 and board2 - 2010 you will se it has more families

I have been going crazy with this for the past 2 Days

But still didnt give up!!!

[Fitch]

Try the attached. Note that the global year fields are now set to trigger a script to refresh the window -- this was necessary to force the bottom portal to update.

Computer Shop5.zip

[Walter-MainFrame]

Nice Job !!! Fitch How you did manage it so fast ??

I Tried it Works in all Ways !! You just saved me the weekend

I am trying to understand the filterportal formula its a a bit complex if you could just document a bit you be perfect...

But I dont want to take too much more off your time

Thanks again

Walter