Jump to content
Sign in to follow this  
bleapy

Portal to display data from a Second Level Relationship

Recommended Posts

bleapy    0

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
eos    225

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
bleapy    0

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
eos    225

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
bleapy    0

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
eos    225

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
bleapy    0

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
eos    225

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
bleapy    0

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
bleapy    0

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  

  • Similar Content

    • By HJS
      L&G,
      I have read the guidelines for posts in this forum. Therefore I did not enter the Post title that came first in mind: Do I understand portals (aka do I understand Filemaker) and I do not emphasize now on being new in Filemaker issues.
      I am trying to achieve something simple at a first step of a big plan: Entering recipes in a Filemaker testversion database. 
      The longterm goal should be a database in which I can enter my collected recipes.
      Followed by tool for a weekly menu plan for my family and a shopping list coming out of the database after taking care of my fridge's stock => so far so good, but currently this is a plan for the next ten years I realized starting with Filemaker.
      I searched different apps and database programs, and Filemaker seems to be my solution as I did not find anything that fits exactly my expectations.
      So back to the first problem: how to enter my recipes?
      I created a table of recipes with an unique ID_pk (I learned already about pk and fk), Name and How-To-Make-it as well as IDs(fk) from the other tables which are Zutaten (Ingredients) with the name and the amount, Form_Zutaten (how the ingredients are used i.e. chopped, sliced, etc.) and Einheit_Zutaten (unit i.e. cup, liter, etc.). Why I have choosen this structure: because all ingredients must be combinable with different units and how the are used. otherwise I have to enter e.g. avocado sliced, avocado mashed, etc. or make rules like liquids can only be liter or mililiter or cups while flour can only be gramms etc.
      So a combination of all three tables should be possible.
      What I though might be an easy task is to create a recipe layout than with 
      recipe ID
      recipe name
      and a dynamic portal depending on the number of ingredients constisting of:
      Zutat - Form - Menge - Einheit
      in english: Name of the ingredient - how is it, which kind/form - how much (qty) - which unit
      so I related the tables via pk and fk, created the layout and bam: complete disaster.
      after needing a couple of days to make the values appearing in the dropdown (learned about the value list), some fields in the portal do show the all entries of the ingredients, while the kind/form and unit fields do always show the first value of the table?
      and although I locked the fields, the first entry is always shown and is getting overwritten by the choice I choose from the dropdown...
      and nothing is dynamic, meaning that 7 ingredients will make 7 lines and it always starts with one empty line, etc.
      I am not sure if my descriptions do make sense, therefore I am attaching my work, hoping someone could help me with how to use the portal for a convenient data entry.
       
      thx in advance!
      br
      HJS
       
       
      2017_09_Essensplan.fmp12
    • By Peter Barfield
      Is it possible to filter a portal based on another filtered portal content where a relationship exists. 
      My question stems from a scenario where I have a jobs table and a plant (being machinery) table. I am trying to figure out a way to filter a complete list of plant to only show those that are not allocated to a specific day on the job table. 
      I guess I am just seeing if it is at all possible or is there a better method to do it.
    • By DreadDamsel
      Hi, I have a bit of a dilemma that I've tried a number of solutions to sort out, to no avail.
      I have a portal that lists the participants in a class. In each row is a checkbox that can be used to indicate whether that participant has withdrawn from the class.
      I've used a summary field to count the number of checked boxes and have a field that subtracts that number from the total of records in the portal - easy peasy.
      Where it gets tricky is that, one of the fields relates to the number of children associated with a participant (a mum may have 3 kids in the class if it's one of the playgroups). Now I need to know how many kiddies there are in the class. I have a field that totals the number of children in total and that works fine, but...
      ... and here's my problem -
      where a parent has withdrawn (and the box is checked), I need to subtract the number of children associated with that parent from the total number of children).
      The checkbox is a value list with a single value, 1.
      All the solutions I've tried so far are fine unless it pertains to portal rows - and I can't figure it out for the life of me.
      Can anyone help me out?
    • By 123
      Hey,
      I have the following problem. I'm working on a solution for the owner of several restaurants. Each restaurant has meetings once or twice a month. How would I structure the tables so that I could have a layout on which I select a restaurant and then inside a portal I get a list of all employees. On the portal for each row I want to have a button or checkbox to mark if an employee is present or not.
      I already have the following tables:
      Restaurants, Employees and Meetings
      So far I have a relationship between the Restaurants and Employees table which I use to assign employees to a restaurant, and a relationship between the Meeting and Restaurant table, which allows me to show all employees that work at a selected restaurant using a portal on the meetings layout. How do I proceed to solve my problem? Do I create another table MeetingAttendees or something similar that I use to keep track of people attending a meeting? How would I create a relationship then to allow me to mark certain employees as present and absent?
      Thanks in advance,
      Mike
×

Important Information

By using this site, you agree to our Terms of Use.