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

    • By fireandlight
      I've driven myself more or less crazy trying to figure this out on my own. The relationships themselves are rather simple: Each project has a list of questions. Each question has a person who created the question, and someone the question is assigned to. A question also has a number of responses associated with it which each have their own "creator".
      There are 3 main tables to cover: Questions, People, and Responses
      People consists of 3 fields: The UID, the person's name, and the person's email address Questions has 2 fields which relate to People: creatorID and assigneeID Responses has 1 field which relates to People: creatorID. The way the relationship graph is set up now, I have 3 instances of the People table that relate the UID field to the appropriate ID field in Questions or Responses as needed.
      What I want to happen is when I set an Assignee or Creator for a question or response, I want to use the corresponding record details from the People table - if one does not exist, then create a new one. Instead what happens is a get a new record in the people table every time. Even if I set the Layout field to give me a drop down of the existing values (and I use it to select one when, say, setting the assignee on a question) it still creates a new record. So confused... any help is much appreciated.
    • By sinisa93
      Hi!
      I have two separate questions regarding portals and how they work. Before I start let me describe what I'm doing. I'm constructing DB for local karate club. I have one table/layout with general info about members, I have second table/layout with records about belts (including color of the belt, when & where the belt was obtained, the mentor of the member & sparing partners of the member etc.) and I have 3rd table/layout that shows various activities of each member (there are also several other tables but not important for this topic). All tables are connected via key field (ID number)
      QUESTION 1
      I have no problem creating portals on 3rd layout to show me which belts particular member has with all additional info from the second table that go along with it. BUT I don't know how to create a portal that shows all members of the club that had particular member as sparing partner. Thing is that I have 3 separate fields for sparing partner because each member can have up to 3 sparing partners and I simply don't know how to connect them all so I can see the list of all members that have particular member as one of three possible sparing partners.
      QUESTION 2
      I know how to place count of related records in filtered portal but I'm not sure how to get a Total of this counts.
      Any help is much appreciated!
       
    • By JoP
      I have a solution with 4 Databases. There is the main-data-database on a Filemaker Server (only data, no relationships in the Table-Occurences (TOs)) and a GUI Database on the Desktop (only Layouts, TO's are included from the Server-Database and on this TO's the relationships are defined, so I can use them in the Layouts. Than I have a 3rd database, which is a subset of the server database and lies on an iPad. This 2 data-databases will be synchronised by MirrorSync. And the 4th Database is a special GUI Database on the iPad which uses the 3rd database for Data. The iPad GUI Database also holds TO's with relationships.
      Now, I need a custom privilege set to forbid changes and deletions of records when they are marked as Locked. To see all tables in the privilege dialogs, I can only do this in my data-databases, because in the GUI databases I can't see the tables in the dialogs. But in the data-databases I have no relationships defined, so I can't use related tables in the formulas. Now I have to add these relationships also to the data-databases. Is there a simple way to "copy" them from one database to an other?
      Thanks, Hans
    • By Volker Gurtler
      The database has table1 with “Sample No” field relating it to table2 with “Sample No” field. Table 2 has a portal on a layout with Table1. Each portal row has the Category field with one of 3 values (MF,BP and CC). There are about 7000 samples. Each sample has from 1-12 portal rows displayed with 1 or more occurrences of the 3 category values one of the values shows the count of each value per sample.
       
      The question is how do I write a script so that every sample has all 3 category values (MF,BP and CC)?
       
      I have the following scripts for when there is only one value/portal - one for each of the 3 values (3 scripts are the same except that “Molecular Function” is substituted for BP or CC and “Molecular Function” = MF):
       
      Perform Find [ Specified Find Requests: Find Records; Criteria: Transcript Database::Regulation: “=test” ] 
       
      [ Restore ] 
      Perform Script [ “Sort by Sample No” ] Go to Record/Request/Page 
      [ First ] 
      Loop
      Go to Portal Row 
      [ Select; First ]
      If [ GO_GOT::Putative Gene Function Category 
      "Molecular Function" ] 
      ≠ "Molecular Function" ] 
      ≠ If [ GO_GOT::Putative Gene Function Category 
      Go to Portal Row 
      [ Select; Next ] 
      Go to Portal Row 
      [ Select; Last ]
      Set Field [ GO_GOT::Putative Gene Function Category; "Molecular Function" ] 
      End If End If 
      Go to Record/Request/Page 
      [ Next; Exit after last ] 
      End Loop 
       
      I can not get a script to work on a portal with only 2 categories so that only one of the 3 categories is added per sample.
      Transcript Database v05 test.fmp12
    • By Ultradistance2005
      I have a database with each record representing one account. Within each account record, there are ten different companies the account may have been sent to (one individual field for each company within the same table), along with the results of that effort. Both of these make up the company data set. Also within that single record there is an identifying ID Code, which represents the type of account it is.
       
      What I want to do now is create a table with a portal where I can select or enter an ID Code, and get each instance of that ID Code with each individual company data set. So if with the individual account record I noted that I sent it to four of the ten companies, I want it to return four responses, one for each of the companies I sent it to, with the unique responses (also in a field in the main account database) with the details of the individual record.
       
      I feel like the answer is right there, but I just can't get it to work. Any suggestions would be much appreciated. Thank you.