jagstirling

Thanks for this. I am an AB-SOL-UTE newbie in Filemaker, but I have convinced my company to buy a licence so I hope this will change. I will search up 'popover' and take it form there.

Hi, I am looking for a simple method to select a week in a year, based on an assumption that there are 48 weeks in a year (see mock up attached, where the grey week buttons are clickable). The calendar picker in FM is great, but not suitable for my requirements, but I WOULD like my requested solution to be accessed in the same way (maybe returning something like YEAR, MONTH, WEEK_NUM. Also, not that bothered if the week are 1-4 for each month or 1-48. Has anything been built that i could leverage? Thanks

Field 1, Field 2 and Field 3 are months. I would like to place them in a Stacked Column chart which I cannot do if they are in three different columns.

Thanks, the data is already in Filemaker, so I don't need to import I want to get the 3 fields into 1 filed so I can chart them

Simple question (I doubt the solution is as simple). QUESTION : Is it possible to do turn data structured like this; Into this;

Hey, Currently I use Excel in the same way as the portal I suggested, with one column per month. Data is just entered under the relevant month with the appropriate OFFICE, CLIENT and RESOURCE assigned in the columns on the same row. I understand that for Filemaker, I should have one column for DATE as opposed to 12 columns, one for each month, and then yet another column for the year. This wa sone of the questions I had. Thanks for sparing the time to reply.

Forum Post Text I am a big fan of Excel have always used it for analysis and reporting but I see the potential of Filemaker (Pro 14 Advanced) and want to see if it really can provide what I need. I am a complete Filemaker novice, but through the forums I have built up a reasonable level of basic knowledge. Also, I have looked at the many ‘sample’ solutions and videos posted here and elsewhere, which are a great help. A background in Excel modelling has also been a great help as the functions and their logic are often the same, or very similar. I have decided to try and convert one of my Excel models into a Filemaker solution and would like to post my planned design online, not in order to get something built for me, but in order to; Confirm what I plan to do is the correct approach Identify possible solutions that I could use (for example, I plan to use portals but would like to understand those a little more) Identify source of material specific to my project that may help me succeed in moving from an Excel based model into a more robust Filemaker solution Please find time to review the simplified detail below, if I can get a ‘steer’ on this then I will add additional functionality myself. Finally, I have invested a bit of time writing this and providing information as I appreciate that all the responses I receive are free of charge and, that, to get the best responses you need to clearly define the problem. If I have not done that, then please come back to me for clarification, and, if you spare time to read this and respond, thank you in advance. Apologies if this is posted in the wrong forum > please move if necessary. Simplified Business Requirement - Background Company XYZ Inc (for want of a better name) is a global services organisation, with offices in London, New York and Paris. Being a global business, it has a requirement to plan the use of its employees (down to a monthly granularity only) across its clients and offices to ensure that; Employees are utilised optimally Employees are not overcommitted (ie. planned to be working for a client in New York and Paris simultaneously) Employees are not overcommitted in terms of planned work in a month (who can work 45 days in a month?) Forecast work for the coming year is available for management review Analysis of planned work by resource and office is available As part of the preparation for this project the Resource Planning solution from Filemaker has been reviewed already, bit it does not contain the functionality required. Also, building from bottom up will be a better learning experience. Current Solution - Data Entry Currently this is achieved through an Excel workbook, into which planned work is entered for the relevant OFFICE, CLIENT, EMPLOYEE, DATE (to identify a month) and the WORK planned for that month. A sample is shown below. OFFICE CLIENT EMPLOYEE DATE WORK C_DATE London ABC Ltd John 01/02/2016 20 29/02/2016 London ABC Ltd John 01/03/2016 20 31/03/2016 London ABC Ltd John 01/04/2016 15 30/04/2016 London ABC Ltd John 01/05/2016 15 31/05/2016 London ABC Ltd John 01/06/2016 10 30/06/2016 Paris XYZ Plc Andrew 16/01/2016 10 31/01/2016 Paris XYZ Plc Andrew 15/04/2016 10 30/04/2016 Paris XYZ Plc Andrew 01/05/2016 5 31/05/2016 Paris XYZ Plc Andrew 01/06/2016 5 30/06/2016 Paris XYZ Plc Andrew 15/07/2016 5 31/07/2016 Paris XYZ Plc Andrew 07/06/2016 10 30/06/2016 New York QWERTY Inc Mary 04/03/2016 15 31/03/2016 New York QWERTY Inc Mary 06/08/2016 20 31/08/2016 New York QWERTY Inc Mary 09/09/2016 20 30/09/2016 New York QWERTY Inc Mary 15/10/2016 15 31/10/2016 New York QWERTY Inc Mary 30/11/2016 15 30/11/2016 London ABC Ltd Peter 31/12/2016 10 31/12/2016 London ABC Ltd Peter 03/06/2016 10 30/06/2016 London ABC Ltd Peter 05/07/2016 5 31/07/2016 London ABC Ltd Peter 08/09/2016 5 30/09/2016 London ABC Ltd Peter 03/11/2016 5 30/11/2016 New York Widget Inc John 01/01/2016 5 31/01/2016 New York Widget Inc John 04/02/2016 10 29/02/2016 New York Widget Inc John 13/03/2016 15 31/03/2016 New York Widget Inc John 18/09/2016 20 30/09/2016 Note, a calculated field is used to convert all the entries in the DATE field into a calculated C_DATE field (using EOMONTH function in excel). This is one so that all entries have a standard date entry for future use. I am aware this can be done in Filemaker. Current Solution - Reporting NOTE ; Reporting does not mean the production of a ‘report’ it can (and does) mean the display of information on the screen. Once the information has been entered the following information is then produced on different sheets using the SUMIFS fucntion in Excel; Summary by Resource RESOURCE Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 TOTAL John 5 30 35 15 15 10 0 0 20 0 0 0 130 Andrew 10 0 0 10 5 15 5 0 0 0 0 0 45 Mary 0 0 15 0 0 0 0 20 20 15 15 0 85 Peter 0 0 0 0 0 10 5 0 5 0 5 10 35 15 30 50 25 20 35 10 20 45 15 20 10 295 Here we can see that John is planned to work 30 days in Feb-16 (assumption is standard 20 days per week). This would be conditionally formatted in Excel to draw the user’s eye. I am comfortable with this functionality on Filemaker. Summary by Client CLIENT Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 TOTAL ABC Ltd 0 20 20 15 15 20 5 0 5 0 5 10 115 XYZ Plc 10 0 0 10 5 15 5 0 0 0 0 0 45 QWERTY Inc 0 0 15 0 0 0 0 20 20 15 15 0 85 Widget Inc 5 10 15 0 0 0 0 0 20 0 0 0 50 15 30 50 25 20 35 10 20 45 15 20 10 295 Here we can see that ABC Ltd is our biggest client in terms of planned work for the coming year, with work of avrying amounts throughout the year. Summary by Office OFFICE Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 TOTAL London 0 20 20 15 15 20 5 0 5 0 5 10 115 Paris 10 0 0 10 5 15 5 0 0 0 0 0 45 New York 5 10 30 0 0 0 0 20 40 15 15 0 135 15 30 50 25 20 35 10 20 45 15 20 10 295 Here we can see that New York is our biggest office in terms of planned work, Paris appears to be a long way behind the other offices. Planned Filemaker Solution - Data Entry It looks like a portal is the best way to get the information into Filemaker, something like the example below where new records would be entered manually. I have already read up about value lists and sourcing data from other tables so I have that covered for the OFFICE, CLIENT and EMPLOYEE fields OFFICE CLIENT EMPLOYEE Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16 Oct-16 Nov-16 Dec-16 TOTAL London ABC Ltd John 0 20 20 15 15 10 0 0 0 0 0 0 80 Paris XYZ Plc Andrew 10 0 0 10 5 15 5 0 0 0 0 0 45 New York QWERTY Inc Mary 0 0 15 0 0 0 0 20 20 15 15 0 85 London ABC Ltd Peter 0 0 0 0 0 10 5 0 5 0 5 10 35 New York Widget Inc John 5 10 15 0 0 0 0 0 20 0 0 0 50 I would like some way of filtering the data in the portal, so a user can go in, filter on their office and then edit, delete or add existing records. Questions Table relationships You can see what I have planned from the Current Solution - Data Entry section, do I need to change this? What about 'occurrences'? Do I need a separate occurrence for the portal and one to hold the information in Data Entry. I plan to have tables holding OFFICE, RESOURCE and CLIENT information to maintain integrity (these will not be free entry fields in the portal. Storing my data in tables Should my Filemaker Table holding the planning information hold multiple columns, say 12, one for each month and another one for year? So, I would have columns, Jan, Feb, Mar , Apr etc etc and then one called Year into which I would enter 2016, 2017, 2018 etc etc. I DON’T think this is best practice and I may come to regret this later but it would make it easier to create a portal. Currently I have to try and ‘cross-tab’ (yes I have read this topic in depth) my data without any success. Also, creating a total column (so sum up the months) is proving a challenge. Summing data in the portal How can I create a total ‘column’ in my portal? Similar to the ones in the Current Solution - Reporting section, that will sum up the WORK values in that row? This is why I am drawn to storing data in 12 monthly columns, as I have the data now I need to perform something like the SUMIFS function in Excel, using, OFFICE, CLIENT and EMPLOYEE as the inputs. Future In the future I plan to add the following features, not sure if this effects the design at this stage; A dashboard charting the data and showing KPIs Skill profiling of individual employees

Thank you doughemi Accessing the sort through the Browse view was the missing part for me

Hi, I am a newbie to FM, pease excuse my ignorance! I have a table of data; REGION | SALES A | 500 A | 400 A | 300 B | 600 B | 700 C | 300 C | 200 I am tying to produce a report that will show REGION | TOTAL AMOUNT A | 1,200 B | 1,300 C | 500 I have attached my meagre efforts but as you see, the report I have built doe snot produce the desired result. I have added a TOTAL SALES field to my table but I cannot get the result that I desire. What have I done wrong? Test.fmp12