Sign in to follow this  
Followers 0
bleapy

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 (
"SELECT SUM(Amount)
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.

 

Thanks

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.

 

 

post-95067-0-68129200-1406141665_thumb.j

post-95067-0-41110700-1406141759_thumb.j

post-95067-0-63189400-1406141796_thumb.j

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 …

SumUp_eos.fmp12.zip

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

    • Relating tables with month and year
      By shevyshevy
      I have two tables Claims and Premium
      Records on both tables have dates
      I want to do an analysis for both tables on monthly and yearly basis with a calculation field
      Analysis (Claims::claims + Premium::premium)
      my objective is to be able to gather all the records for each month  and each year on both tables using that analysis. That is my basic task. There are other fields like Remark, and recommendation but they are based from the result I get from the Analysis. For example, Analysis field is > 120 ,  Remark shows Excellent..  
      please how do I achieve this?
       
    • FM Relationship Error Message
      By Spidey
      In my attachment, when I search for "Cutting tools" in the main table. It produces the following message.  Is there a way I can get rid of this message.  I think I know why because I got my product line through calculation of look up.  
      "This operation cannot be performed because one or more of the relationships between these tables are invalid. "
      fmdata.fp7
      fmdata.fp7
      newfmdata.fp7
    • Find in progress. Processing query... - going slow for only ONE portal
      By jraquel
      Hi,
      New to this so not sure what is needed to help me. I have several portals in my database and they all have filter and sort settings. All the settings are the same on each portal (just pulling from different layouts). Each portal has a button with a script in it that has it "go to the related record." All of my portals and go to related things work great and quick an no issues.
      Yesterday, I added a new portal and a new go to related and updated my relationships, etc. everything mimics the same settings on all my other portals. BUT for some reason this one portal and go to related are lagging big time and everytime I do anything that affects that portal I get the "find in progress. processing query..." and it takes several minutes to process.
      I do not get this my other portals and I'm a bit confused and feeling frustrated that I'm having trouble with JUST the one portal. HELP! What do you all need from me to help me with this? 
    • Portals content in email as table
      By Christopher Michael Binks
      Hi I'm pretty novice at FileMaker but have as solution pretty much operational internally.  
      My company is all based on running bookings with external clients and provider all through email. 
      Currently it's all done manually the admin assistant copies the booking from the booking sheet to a email templat in a table and then sends this out one table to the client, another to the provider of which there can be many.  
      The bookings are trotted up in a portal on the clients page with a new line for each product.  Can I export the portal to a html table to email out generating a new table row for each matching portal row?
      I presume I'm not the only one generating lists of items for email but can't get a handle on how to achieve this without a fixed table and substitution line by line have looked through all the documentation I can find  
      Assistance greatly appreciated
       
    • Conditional Value List in Portal with multiple field values
      By dav1089
      Hello,
      I am trying to create a daily sales call log, for that I have used Portal for which I am using "company" table in which I have "contact 1" to "contact 12" separate fields for each record (company)
      Now, on the portal, I was able to fetch contact names dynamically based on chosen company using conditional value list relationship and creating new text field with auto-enter calculation of List(contact1,....,contact12), but how can I fetch contact number for selected contact name for that company? I created a new field to auto-enter calculation of contact numbers like the same way but it seems like I am going in wrong direction
      It would be great if anyone can tell me as I am learning FM.
      Or is there any other way of doing the same?
      Here is my relationship diagram and layout: