Sign in to follow this  
Followers 0

Portal to display data from a Second Level Relationship

10 posts in this topic

I am having a problem in showing data from a second level relationship (not sure if this is the correct term).

Here's the scenerio.  Every year, I need to create new funds and expenses are created (PO, Direct Payment, Credit Card, Travel Expenses, etc) However, PO and Travel Expenses can be split funded by multiple funds. So when an expense is created it's not tied to the Fund, the Expense ID is created, and with in that the Expense Line. The Expense Line will contain a Fund field to tie it to the Fund Table.  So my table relationship looks like this


Fund --< Expense Line >-- Expense


Now I want to show all the Expense related to the Fund table and Sum up all the Expense Line that is funded by the Fund. I have a field in the Expense Table "Total Fund" which is a ExecuteSQL:

ExecuteSQL (
FROM Expense_Line
WHERE KF_ExpenseID = ? AND KF_FundID= ? "
; "" ; "" ; EXPENSE::KP_ExpenseID; FUND::KP_FundID



However, this was not pulling up the right data. Any suggestions? Let me know if you want to see the file on what I have now.



Share this post

Link to post
Share on other sites

When you're in the context of Expenses, which primary Fund key do you expect to see via LineItems?


Sum up all the Expense Line that is funded by the Fund


Yes – which Fund?

Share this post

Link to post
Share on other sites

In the Expense table, there are no direct relation to Fund.  But when I'm in the Fund Layout with the Expense Portal, I figure that the SQL will be able to Pull the Current FundID that I'm browsing. Maybe that's not the case?


Maybe, I need to have a Global Field for the Current Fund ID that I'm browsing?

Share this post

Link to post
Share on other sites

Actually, I'm not sure what it is you want to calculate in which context:


• when you browse through Funds, do you want to 1) see the sum of all line items related to the current fund, or 2) the sum of all line items related to the current fund and a specific Expense?


• when you browse through Expenses, do you want to do the same (in reverse)?

Share this post

Link to post
Share on other sites

EOS, I want to sum up all the Expense Line related to the current fund and a specific Expense.


Layout w/Fund Table

Will contain Fund Details (ie Beginning Balance, Fund Description, Year, etc)

 --- Expense Portal

      --- Portal Lines will contain the Expense details, and the SUM of Expense Line related to the fund and Expense


I've attached 3 screen shots. Expense Layout, Fund Layout 1 and Fund Layout 2

(Fund Layout 1) Fund ID 1 Total is correct in the (Expense Tab) Portal Actual is Correct $113, but when I go to FundID 2 (Fund Layout 2 Screen Shot) the Actual should be $1000, but it remains to be $113.


In Fund Layout, I would like to show the sum of all the expense line that are related to the fund (Box on the Top Right)

When I'm browse through the expense I don't need to see the sum of the funds, but it will show the sum of the related expense Lines. Which I have already.






Share this post

Link to post
Share on other sites

I've attached 3 screen shots. Expense Layout, Fund Layout 1 and Fund Layout 2

(Fund Layout 1) Fund ID 1 Total is correct in the (Expense Tab) Portal Actual is Correct $113, but when I go to FundID 2 (Fund Layout 2 Screen Shot) the Actual should be $1000, but it remains to be $113.


Why? There are the same travel line items in both fund records, adding up to $113 in each case. Where is the figure of $1,000 supposed to be coming from? – I can see a line item in the Expenses table of $1,000 for fund #2, but that doesn't show up in the fund record. Are you sure your relationships are correctly defined?


I guess I'm not understanding the setup, or the relation between a Fund and an Expense as you use them; sorry … 


btw, if you use ExecuteSQL() in a field definition (which I was given to understand may not be the best practice anyway), be sure to make the field unstored, or the results won't update properly.

Share this post

Link to post
Share on other sites

Sorry for not being more clear. I'll trying to explain. And thank you again for taking the time to help with this. 


Regarding the 3 Screen Shots.


Here's the process I had in mind steps.

1.  At the beginning of the year I would create a Fund in the Fund Table.


2. Create an Expense in the Expense layout by clicking on a Button in the Fund Table. The button uses a script to go to the Expense layout and create a new record, captures the FiscalYear field pulled from the Fund and enters it into a FiscalYear field in the Expense table. The FiscalYear does not tie the Fund and Expense Table together.


3. Once the Expense is created, I'll be entering items into the Expense Line item portal.  Each Expense Line will have a Funding Source, which will be a Filter Drop Down Value to get the Fund ID that's available in that particular Fiscal Year.  So, if I had purchased 2 computers on the same PO, I would be able to charge 1 computer to Fund 70000 and 1 computer to 48110. If you look at the Expense screen shot, $113 is charged Fund 70000 (FundID 1) and the other $1000 is charged to 48110 (Fund ID 2).


What I want to do is be able to see the Total of the Expense related to the Fund that I'm browsing. So Expense Portal (Expense Tab) for Fund ID 1 should only show a total of $113, and Fund ID 2 will show a total of $1000. But that does not seem to be the case. And I can't figure out why.


I've attached a screenshot of my relationship.


4. Based on the relationship, I can create a Portal of the Expense Line in the Fund layout, I believe that would calculate the correct value, because it has a direct relationship using the FundID as the common key. But I don't want to see every Expense Line.


Can you send you the FM file and take a look at what I have?


Thanks again!

Share this post

Link to post
Share on other sites

OK, why don't you take a look into the attached file? Maybe this will give you some inspiration …

Share this post

Link to post
Share on other sites

EOS... From the looks of it, your setup would work for me. But now I'll need to figure out what you did and implement it on to mine. I'll keep you posted. Thank you.

Share this post

Link to post
Share on other sites

EOS, the sample you gave me was GREAT. I didn't use the structure entirely, what I was missing was the Global value to establish a 2 value relationship in another TO. Thank you for your help!

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • By fyanesv
      Hi, I’m really Stuck , any suggestions would be most appreciated.
      Problem :
      I need to replicate a particular behavior of the PopOver that is inside a Portal.
      When inserting or editing portal Rows via popOvers, the data updates in the portal before “committing”. 
      Trying to replicate this behavior according to this rules: 
      -. Using Transactions and Data modeling.
      .- Data not updated at the portal before committing (refreshing portal by trigger or exiting a field doesn't work).
      .- Data not updated at the portal as inserts or edits are been made (same as above). 
      .- Inserts or Edits are made outside the portal in the same layout (no modals, Using SlideControl).
      .- Not using PopOvers inside portal.
      .- Portal is not accesible by user it only shows the list of items been inserted or updated.
      I have tried different approaches but no solution yet, trying to replicate behavior has been very frustrating.
      Filtered Single Line portals and Magic Key with different Techniques and different relationship approaches, I have not tried Globals via Scripts which I presume could be a solution but don’t have much to start with!!!.
      For the record:
      A few months ago I started implementing “Selector Conector and Transactions”,  I´m not so sure I have correctly implemented this.
      I have prepared a Sample file (is not the actual solution) with very simple use of Transactions and Data Modeling, it shows different approaches I have tried. Used some technics learned here at this forum which have been very helpful.
      Approaches number 1 and number 2 from the Sample file are not the preferred, but merely show the behavior I´m trying to replicate.
      A script at start creates small window that shows records are been created in child's table even tough it won’t update the portal before commiting when using technics 3 and 4 from the Sample file.
      Hope my explanation makes any sense. 
      Thanks in advance, and cheers to all 
      PS.: Check the green Framed Portal at the right hand side of the layout´s Sample file for a cool technique that I designed, it uses  “Hide Object when”, and a combination of formatting the Button and portal row (Padding), which makes the illusion of an X circle appearing on the right side when Hovering portal rows, mostly used for deleting. Hope any one has a use for it.
    • By laurend
      I created a database to track permits by building. Each permit type is stored in its own table. The only link between tables is the Building Name which is the physical location where the permit exists.
      v_Buildings (fields include building name, ID, building status, address, city, state, zipcode)
      Air (ID, Building Name, Expiration Date, Days_to_Expiration, Type, etc...)
      ...9 permit types in all, and I will add more in the future
      Each of these tables has the same fields in common:
      I created a Dashboard layout with the idea that users could select a building name from a drop-down menu and then see all of the permits for that particular building along with their expiration date and the permit owner's name. I am just baffled as to how to do this across multiple tables. I have looked at SQL, Join tables, portals etc...., and I cannot figure out how to aggregate all of this information into one view for users.  I cannot even seem to figure out what table should be used for the Dashboard layout. I would love to hear from the community the best and hopefully scalable approach for designing this layout.  Thanks in advance from a novice user.
    • By mbarrett65
      I'm using a field-based value list in conjunction with a filtered portal based on a relationship. The filtered portal seems to be working fine. However, "1" and "0" are showing in the value list though neither of those values are in any records.
      Issue #2 is that unless I include "IsEmpty (fieldname)" in the calculation field used in the relationship, all my portal fields have to contain data or the record doesn't show in the portal. In other filtered portals, I've never added "IsEmpty" to the calculation field and records have shown in the the portal regardless of empty values (see yellow highlight in screenshot).
      Thanks for any insights on these two issues.

    • By Guy_Smith
      I can't figure out how to subtract the total of several child records from a starting value in a parent record:  Sounds simple, but it's got me stumped!
      My database is designed to issue Emissions Reduction Credits to companies for reducing or eliminating sources of air pollution.  Those companies then later use those credits to offset new construction, sell them to other companies for cold, hard cash, transfer them to other companies, etc.  The initial issuance is all on one Certificate and there can be between one and five pollutant credits on each individual Certificate.  A company can own many certificates, but a certificate can only belong to one company.  Once issued, a company can use the credits all at once or (more likely) a little at a time.  After each use/expenditure of credits, the certificate is reissued with the new balance of credits listed on it.  Thanks to a lot of great help in the Relationship subforum (Thanks again, Don and Bruce!), I've got the basic architecture down and issuing the initial credits works just like it should and the FIRST instance of credit usage calculates the new balance correctly, but when subsequent uses/expenditures are entered, the new balance ignores any previous expenditures.
      I've tried dozens of different calculations, added TOs, lumped all transactions into one TO, filtered portals, and barked at the moon, and nothing works (though I did manage to scare the bejeezes out of my cat!)
      I have attached my semi-sorta-solution to help clarify my problem.  It should open to the "Certificate" layout - the bottom portal has the usage data that isn't calculating the new balances correctly.  Any help in getting this up and running would be most appreciated - thanks in advance for sharing your time and talents.
      Best Regards,
      FMPA 15, Windows and Mac
      ERC Registry v2 ModBFR Bad ERC Balance.fmp12
    • By Roger Tuan
      Is it possible to sync data in a Filemaker portal to an external MySQL database? For example, an invoice listing multiple products stored in another table (assuming I only want the information in the portal, such as qty, name, price).
      Or do we have to manually sync all related tables behind the scenes and then re-create the relationships using joins later?
      Thank you!