Jump to content

John Kostenbader

Members
  • Content count

    16
  • Joined

  • Last visited

Community Reputation

0 Neutral

About John Kostenbader

  • Rank
    member
  • Birthday 11/06/1964

Profile Information

  • Title
    Vice President
  • Industry
    Medical
  • Gender
    Male
  • Location
    Easton, PA
  1. John Kostenbader

    summarizing data in a portal timesheet database

    Wow...both excellent suggestions. I'm not really great with the ExecuteSQL function yet (I'm practicing). I find the portal approach very fascinating too and I'll probably try that first (very creative). Thank you both for your suggestions John The portal suggestion works excellent. I'm curious though....what would be the procedure to attached to the "Type" field to refresh the summary fields in the portals. I calculates well if I leave the layout and return but I'd like to add a script trigger to the type field to update the calculations if it changes
  2. John Kostenbader

    summarizing data in a portal timesheet database

    Really good question. I suspect I will want to further manipulate it. For instance I will probably want to take the summary of "support" time and add a calculation to indicate this is at time and a half John
  3. Consider a standard timesheet application with a parent "timesheet" table and a related "timesheet details" table connected with a one-to-many relationship. On a layout based on the timesheet table I have a portal that outlines the details for a two week pay period. One of the elements on the timesheet details is what type of entry is it (i.e. regular hours, support hours, etc.). I would like to be able to summarize the hours of each of those entries on the timesheet layout. I have been able to create a calculated field that does sum the total hours well and I thought if I made the calculation something like this: If ( Timesheet_Details::Type = "Regular" ; Sum ( Timesheet_Details::Hours_With_Lunch )) It would only summarize only those timesheet details that had "Regular" in the "Type" field. Alas it does not work. Not sure how to approach this. I tried a summary calculation but it does not allow for summarizing specific data (seems all or nothing). Would appreciate some direction on this. Ideally I'd have three calculations on the timesheet layout for "Regular" hours, "Support" hours (which are calculated differently and "WFH" hours.
  4. John Kostenbader

    Use of Portal on Unrelated Table on Main Menu

    Yes...I may have not described this correctly. There is a many-to-many relationship between employees and comments via a join_EmployeeComment table and your right, I wrote this post without actually looking at the underlying structure of the main menu and it is connected to a "preferences" table (sorry that should have been correct the first time). I have a single occurrence of the "preferences" table in the relationship graph but it is not in any way connected to employees or comments. I guess that's the technical issue I can't wrap my head around. How can I make a meaningful connection between where there is no real related data John
  5. I've been thinking about this problem for some time now and I can't seen to wrap my head around a solution. I have a standard employee database that of course keeps employee data and in a related table "comments" about that employee. When I put an email out to all my employees I add that as a record in the comments table. There is a many-to-many relationship between "employees" and "comments" because each employee could have many comments and each single comment could apply (potentially) to more than one employee. The comments table has a "category" field which I use to detail whether its a public comment ("hey...everybody clean the coffee pot after your done") or a more private comment about their work performance. Anyway...all that background leads up to a feature I'd like to add to the application. I would like to add a "recent comments" portal to the main menu of the application. The idea being that each time they open the app they have the top 10 or so recent "public" comments they can reference. The technical issue is the main menu layout is not based on a table. Its mainly navigational in nature and does not store any data. Of course when you go to add a portal it wants some sort of table to reference. There does not seem to be a way to add a portal and assign data within a script so I'm wondering if anybody has run into a similar problem and how they might have approached solving it. One thought I had (although its implementation is incomplete in my head) is I can capture the username as the employee signs in and that could be used to identify the employee, which in turn bears a relationship to comments. Thanks in advance.
  6. John Kostenbader

    Basic Report Question - Newbie

    Wow...extremely helpful. Thank you so much for taking the time and providing some clear directions. I hope to expand on your example to do some others Regards John
  7. John Kostenbader

    Basic Report Question - Newbie

    Yea...I screwed that up...my thinking and typing were not in synch and I was trying to put these thoughts down between meetings. Your presumption is correct. It should read "Account Name is in a related table". Sorry about the confusion John
  8. John Kostenbader

    Basic Report Question - Newbie

    I find that reports are one of the most challanging things in Filemaker to understand (at least for me). I have basic projects table and all I want to do is print out the list of projects but grouped by the account they are associated with. I.E. ABC Company Project 1 Project 2 Project 3 Ideally, I'd like a sum of the project value field. I'm confused about the report "parts". I've gone through the wizard like creation process and cannot get them "grouped" by Account name. Now Project Name is in a related table. The only thing in the projects table is the ID_Account. Can someone guide me what fields are supposed to be in what "parts" of the report. I would presume that "project value" should be in the subsummary part. Fields for the project details like status and category would be in the "body" part and the Account Name would be in the header but its not working for me. All I get is a list of projects (not grouped properly). Any insight or direction would be appreciated John
  9. John Kostenbader

    Help ExecuteSQL Newbie

    Extremely helpful Kevin...thanks so much for taking the time to answer this question.
  10. John Kostenbader

    Help ExecuteSQL Newbie

    It did help...Ultimately this is what I came up with which works in the Data Viewer: ExecuteSQL( "SELECT COUNT(ID_Projects), Project_Category FROM Projects GROUP BY Project_Category ";" ";"") In the data viewer I get the results I expect however, when I plug this into the "slice data" of a pie chart, I get nada. I have changed the data source to (current record - delimited). Not sure what else I'm doing wrong at this point. I can also see the value (now) of not adding spaces to your field names. Kind of a pain to deal with in this context John
  11. John Kostenbader

    Help ExecuteSQL Newbie

    I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table. I have a simple projects table that contains ID_Project and a field called "Project Category". I'd like to create a pie chart that represents a count of each category but I can't even get the base select statement correct. Here's what I've done already ExecuteSQL ("SELECT ID_Projects, \"Project Category\" FROM Projects GROUP BY \"Project Category\"" ; "" ; "" ) All I get is the dreaded "?" I've tried a million variations of this and still cannot get it. Once I figure out the base select statement the challenge remains to count them. Any assistance would be really appreciated. Also does there exist a good reference for this function with lots of examples (I learn best from the examples) Regards John
  12. John Kostenbader

    Import XLSX to Child Table

    Excellent observations. Can you briefly expand on option #2. I'm not familiar with Validation as you are describing it
  13. John Kostenbader

    Import XLSX to Child Table

    Sure ...this is one of the files (they all contain blank lines) 91-92 ground truck checklist.xlsx To make things even clearer....here is the results in table view. the top image is, of course, the first blank record imported and the second image is the bottom blank records
  14. John Kostenbader

    Import XLSX to Child Table

    It has been covered here but I really can't find a sufficient answer. When I do an automated import of an xlsx file that has no blank lines at the top into a portal that is part of a child relationship I keep getting blank lines. I've confirmed that there are no blank rows in the excel spreadsheet. For the particular import I'm talking about i get one blank line at the top (portal is not sorted in any way). I also get two blank lines at the bottom. I can almost understand the blank lines at the bottom (how does FM know when to stop?) but I cannot wrap my head around why I have one at the beginning
  15. John Kostenbader

    FMSP Accounts ---> Comments functionality

    Yes...sorry for the deep question. It bothers me I can't figure it out but I didn't immediately think of the script debugger. I will try that and see if I can piece the missing puzzle together. Thank you for responding John
×

Important Information

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