Stu412

Hi all Hopefully this is in the right place, apologies if not. I'm using the technique found in the FM 13 advanced training series to generate virtual lists. I'm using virtual lists to quickly transfer data to a scratch table used for reporting. I have an ExecuteSQL formula to gather some of the data in question: ExecuteSQL ( " SELECT GroupID, SUM(RoundedValue) FROM DataTable_Home WHERE CustID_FK = ? and Code < ? and PeriodNumber = 2 and (GroupID = 68 or GroupID = 3) GROUP BY GroupID "; "|" ; "|" ; Cust_Home::CustID_PK; "500" ) This generates in most cases a dataset of: 68|-3600|3|-7200| <<< as an example for this customer with 68 and 3 being the required group 'names' or headers. This data is passed via scripting into a field on the scratch table called ListData which has several fieldsdedicated to particular values, two of which are dedicated to Group68 values and Group3 values. The formula in each is: Get Value(ScratchTable::ListData;2)<<<For Group 68, -3600 Get Value(ScratchTable::ListData;4)<<<For Group 3. -7200 However, there may be cases where a customer does not have any values associated to either 68 or 3 which obviously reduces the return on the dataset and makes the parse section inaccurate. On my scratch table, I have several fields If a particular customer has NO value for group 68, the returned data set instead looks like: 3|-7200 and this will break all downstream GetValue(Field;x) calculations. So, what I really need to have is a reliable way of ALWAYS returning 4 pipe separated results in the situation that a customer has data missing from one or other of the groups, and that's where I'm stuck. If I could get to: 68|0|3|-7200 or 68|-3600|3|0 as an example, this would enable me to parse the 0 values to where they should be. Any help on this one greatly appreciated. Thanks

Hi all I'm trying to work with the Save as PDF script step routine in a multi user environment. The idea is that a user will click a button and save a PDF (which has a pre defined name)of a generated report to a location on the file server which they themselves specify. This is the part I'm having problems with. I've looked at the Filemaker tips and videos and can see how to structure a static path using variables. However, the use case I have is that the PDF generated can go anywhere the user pleases, so ideally what I want is a 'Save as' dialog box to appear to allow the user to navigate through the network to the desired location. What is it I'm missing here? Thanks in advance

Hi there I have a use case where it's necessary for FM to run a script which updates records - the script takes around 10 seconds to complete. I want to run this on an occasion when a user edits or adds to a portal set of records. I could just use a script trigger, but as I say, this would fire the user into a 10 second update quite frequently and get annoying very quickly. Instead, I want to run the script just once and update all records which are changed in one go. In order to do this, the user will click a button labelled 'Commit Changes' and it's this I want to draw their attention to. When the user edits or adds a record, I want a flashing box to appear around the button. Has anybody got any ideas on how to do this, or indeed, whether it can be done? Thanks

Hi there I have a data table consisting of values which are summarised into some very complex reports. Under normal circumstances, running these reports is time consuming and therefore a poor user experience. As it happens, 80% of the data on a report is historical and will *mostly* not need evaluating. Therefore, I've developed a script which runs overnight to create simple values stored in number fields on a related table. I report from this instead. This means the report is 80% quicker on loading. All well and good....but, there is still a chance that data could change in the period between the scripts being run at night. If the data changes, the related table is then out of date. What I need is a second, smaller script, very lightweight which will update figures on the related table only if they change by user intervention. This means they're still accurate and the final reporting is still quick. The trigger would be something like (psuedo logic): If (Stored value on the reports table) is not equal to (the summary field it originally came from), then run the update script to make it equal OR as an alternative If (value on the data table) is amended today, then run the update script to ensure the reports table number is accurate I feel there's something I can do with a timestamp routine, but cannot get my head around this. Thanks in advance

Hi Steve Thanks for the reply, The custom menu sounds like a neat idea, I'll have a look at that one. Regarding the index, it's basically a part of the title, so a report may be called 'Sales trend over five years' and have an index letter of A, which appears as 'Index A' on the report (top right). ( Sorry for the use of 'Index' as a term, it's not to be confused with the traditional FM sense of the word!) The idea is that the user can enter A, B, C, D etc at some point, that's what I'm not 100% certain with yet. The user will know which reports they want and in what order, so for example, if there's a report which is required to appear BEFORE 'Sales trend over five years', this instead would take 'Index A', as defined by the user, who would then reassign 'Index B' to 'Sales trend over five years' on a manual basis. If I could get that running automatically, that would be great, but FM would need to know which the first report is and how far to go with the lettering....so to start with, I'm keeping it simple! Thanks

I have several reports which I'd like the user to be able to select from a list and then have FM print overall upon one button press, rather than the current set up I have which is a button on each report which fires the relevant print script. To add a little layer of complexity to the process, the reports need a letter index added to them prior to printing. This is because, depending on the circumstances, in one instance Report XYZ could have an index position of say, 'D', but the next time, due to an additional report being required, Report XYZ would have the index position of 'E'. This index position needs to be visible on the report. The reason I'm posting is because I'm not really certain where to start on this one. The one thing I do have is a table which lists all the reports and currently allows a user to enter any report via a portal list based on that table. Any thoughts or suggestions greatly appreciated on this! Thanks

Hi ggt667 I think I see what you're saying here, but so I'm clear, let me explain it back to you. Are we basically saying that Filemaker won't evaluate the calculation if the numbers that go into the calculation haven't changed? Or have I got that completely wrong? Thanks again

Hi there I've been banging my head all day, several days in fact, to create a sub summary report which can reuse records, have a part appear only once and allow me to assert more behaviour over the parts themselves. To complicate the matter it's in a crosstab format as well. I've seen on other posts that what developers do occasionally when preparing reports is to create a temp table of sorts to either store just what records are needed, or to store them in the way they're needed as well. The solution I have at the moment uses lots of summing up and calculations and is intolerably slow to load locally (10 seconds at worst) although it does give me what I need. I obviously want to improve this so ultimately a second table related to the parent with just what I want, just how I want it sounds good. At this point I will add I may have this completely wrong - apologies, it's an idea I'm throwing around! The whole reason I'm looking at this is because I cannot get my sub summary report to behave the way I need it, which may be a failing on my part, but it's a pretty complex report as I've said. So, in concept, is this whack or normal?

I have a need for a lot of cross tab calculations and have referred to the FM 13 Advanced Training Series, lesson 38 which covers this. I've used it for a while now but it's brutal in that you need to create two calculations for each individual 'cell' you wish to display: One calculation tells Filemaker what should be summarised, usually via a CASE statement. The other is the Summary field itself which appears on the layout. I tend to require 10, sometimes more columns which are based on date fields from an overall record list of 70000 so far. Is there not a better way of doing the cross tab calcs, something like (psuedocode) as I'm wary of fattening up the fields on the DB: Calc1::DoSummary (Case (DatePeriod = 1 and SortField = "Tourist" ; Amount)) Rather than two calculations: Calc1 ::Case (DatePeriod = 1 and SortField = "Tourist" ; Amount) sCalc2L::Summary Calc1 Thanks all

Patterncount is working as I need it to on this and I've improved the CASE statement to ensure it doesn't stop at the first true result. Thanks everybody for your help on this one.

Hi there, I have a field which needs to return a value based on the contents of other fields around it, so I'm using a CASE statement to achieve this. Nice and simple, write it once and forget about it. Case (Code = 1 ; "Income Band A" ; Code = 2 ; "Income Band B") I want to add something to the CASE statement which evaluates another field based on text contents, so I have tried to use the POSITION command in the CASE statement to make this work, but no luck so far. Here's what I have: Case (Code = 1 ; "Income Band A" ; Code = 2 ; "Income Band B"; Position (Description;"TOURIST";1;1) = "Tourist" ; "Tourist") What I'm trying to do is to evaluate the Description field for the presence of the word 'tourist' anywhere and then have this returned via the case statement. Any help appreciated, thanks in advance

Hi there I need to put together a sub summary report from a list of data which includes several 'grand summaries'. The basic report layout would be something like this, where each record has a sorting field present to identify it as either 'Group 1', 'Group 2' etc... I'm struggling because, in order to get the Group 1 and Group 2 Summary for example, I need a further layout part against which to hold the calculation - or do I ? That's the question I'm stuck at and seeing as the same sort of thing occurs further down the page, the report is a non starter at the moment. So what I need to do is add together previously summarised results of specific groups and have the results appear in a specific row on the report. Anybody else have to do this? Do you need to create a part which is based on a field with nothing in it (which would make the new part appear under each group, which I don't want)? Very confused here, apologies! Group 1 record Group 1 record Group 1 sub Total Group 2 Record Group 2 Record Group 2 sub Total Group 1 + 2 Grand Summary Group 3 Record Group 3 Record Group 3 sub Total Group 1+2+3 Grand Summary Group 4 Record Group 4 Record Group 1+2+3+4 Grand Summary

I'm looking to see if there's a better way to do what I'm currently doing, which is effectively making cross tab tables from a single column of data. My main data table columns are (in a very simplistic format for digestion here): Date Amount TrueFalse1 TrueFalse2 TrueFalse3 There are around 70,000 records, each with entries in every field above. Records tend to be grouped into months and I need to get columns per month on a report. To do this, I use CASE statements initially and then summarise the results: JanTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Jan" ; Amount) FebTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Feb" ; Amount) MarTotal::Case ( TrueFalse1 = "True" and TrueFalse2 = "False" and Date = "Mar" ; Amount) sJanTotal sFebTotal sMarTotal I now have 6 fields just for three months with this logic testing. I also need to have other variations on TrueFalse1/2/3 per month as well, so the numbers of fields can easily bloat. In the humble opinion of those with more FM knowledge and experience of me, is this an efficient way to do things? Is it more efficient to use a 'temp' table and have this reload data each time I need it to? Any input appreciated, thanks in advance

Hi there I've created several sub summary reports which require a column set up which needs to be aligned to the right, ensuring it's straight, good looking and professional. I have to put percentage figures alongside the values and when these are viewed on the normal layout, they're (mostly) ok. When I print preview, or when I print, the percent sign gets chopped straight down the middle, as if either the cell is too small or the padding is incorrect. I've tried adjusting both the size and the padding, to no avail. When I remove the right align on the percent cell, the problem goes. However, I need these percentages to be organised as well and so far this is not working. I've added a couple of images to illustrate, the first from the screen, the second from the preview page. Hopefully somebody else has seen this and has some advice Thanks in advance

Comment, You've answered my question perfectly thank you - I suspected that records were read only once and this is the case. I need to display what's effectively a two part report, as you'd look at it in on paper. It's a crosstab report which is similar to that demonstrated in the FM advanced training series The top part is showing costs in columns across periods and I've used my records on their one usage to generate this section. CurrentPeriod PreviousPeriod Previous2Period Previous3Period The bottom part shows the period on period change in the costs in the top part. Because my report is set out by columns, each one representing a period, I can do further calculations to show the changes required, ie: CurrentPeriod - PreviousPeriod PreviousPeriod-Previous2Period Previous2Period-Previous3Period These I've done directly on the data table and placed them into a sub summary by customerID (which is the same for every single record in the found set) I won't be duplicating records, thanks for that. I will put together a few calculations though which require some legwork against the deadline! Thanks