Rramjet

So I have had a look at the solutions you pointed to Comment and there is some promise in them, however there are some problems I have with them: First, in the demo files, questions in the same survey have the same response set across all questions, but in my surveys the response set varies across questions. Second,I have free text responses mixed in with the standard questions. Third, (and it may just be me) but the demo files seem to be locked so that I cannot see the calculations, scripts, relationships or experiment with them in any way at all - is there a reason/solution for that? Regards, Rramjet

Okay, sorry for not being clear …I’ll try again… For one client there are: Eight possible survey types: Survey_1 Survey_2 Survey_3 Survey_4 Survey_5 Survey_6 Survey_7 Survey_8 Each survey can be conducted by 5 different respondents (completing the survey in reference to the client): Survey_1 (parent_1) Survey_1 (parent_2) Survey_1 (teacher) Survey_1 (self – is the client...) Survey_1 (Other) Survey_2 (etc) … … Each respondent can complete a survey for one of three age groups: Survey_1 (parent_1) (1-3) Survey_1 (parent_1) (4-7) Survey_1 (parent_1) (8-11) Survey_1 (parent_2) (1-3) Survey_1 (parent_2) (4-7) Survey_1 (parent_2) (8-11) Survey_1 (teacher) (1-3) Survey_1 (teacher) (4-7) Survey_1 (teacher) (8-11) Survey_1 (self) (1-3) Survey_1 (self) (4-7) Survey_1 (self) (8-11) Survey_1 (other) (1-3) Survey_1 (other) (4-7) Survey_1 (other) (8-11) Survey_2(etc) … … Now each of those 15 types of Survey_1 above (and of course the 15 types of Survey_2, and 3, 4 … 8) has a similar set of about 30 core questions plus a handful of specifically tailored questions. Then all of that is repeated at time 2 (a follow-up survey set that is the same as the initial surveys – with a few minor differences we can ignore for now). Now all that is laid out in my previous post in the screenshot .jpg I provided. I have created all the Survey_1 (x15 types = about 500) fields (in a single table named Survey_1_time_1). Now I want to repeat that for time 2. Since most of the fields are the same at time 2, I thought I could simply duplicate the Survey_1_time_1 table, change the name to Survey_1_time_2, copy and paste the tab structure under Survey_1_time_1 on the main Layout (as shown in my .jpg) into the Survey_1_time_2 layout, reassign the fields from the Survey_1_time_1 table to the Survey_1_time_2 table, then copy and past that modified tab structure back under the Survey_1_time_2 tab in the main layout. Unfortunately I can only reassign one field at a time - and as I have 500 fields for Survey_1_time_1 that takes too much time (especially as I have 30 time points!) - hence my question about the ability to reassign fields in bulk. But reading between the lines I assume that is not possible (otherwise someone would have said). So, I need another solution. (oh, each client can have more than one episode. That is they can begin the survey process from time 1, complete it to a certain time point, and the case will then be closed. Later, maybe years later, the same client comes back, a new episode is created and they begin the process all over again) Now as I understand the solution you are pointing me to, I can have (an episode table?), related to a client table, in turn related to a survey table, related to a time-point table– but I cannot get my head around the logic of how it would work. Perhaps you could explain more clearly to me?

Perhaps I should have included a screenshot up front... For the SDQ outcome measure, at Assessment, for a Carer, who completes for the 3-4 age group you can see the fields needed. The Teacher, Parent, Self, etc are slightly different in that different questions are asked reflecting their status - so I need 3 x the agegroup fields plus all those again for the respondent type - and then I basically have to repeat those for all time points. My initial thought was, because all time points basically ask for the same information, I could simply duplicate the Assessment table (with some minor field adjustments ) and copy the Assessment tab structure and paste it in under the 6m (6mths) time point and then re-assign the fileds to the 6m table.. That is slow going so my next idea was to paste the tab structure into the 6m layout, making the re-assignation task that much easier - then paste it back...but it is still a slow task - so my question about whether there was a facility to re-assign the field names in bulk (they are the same names and in the same position in the table - so all it should take is an automation of the manual double click, select the table, hit enter and move to the next field). If I read you (and Comment) correctly I could do away with the timepoint layer and make it a field instead - but then that would require a new record (and I'm already doing that for Episode) and some pretty fancy search procedures up front to make it work. but maybe I am missing something - and if so, perhaps you might be so kind as to explain what?

Okay, perhaps I have not explained clearly enough. Above the tab structure I have client info (Name, DOB, etc) Then the tab structure under that: LEVEL 1: Outcome measure(OM)1, OM2, OM3, OM4, OM5, OM6, OM7, OM8 LEVEL 2: Assessment, Follow-up1, FU2 > FU30 LEVEL 3: Self, Parent1 Parent2, Carer, Teacher, Other LEVEL 4: AgeGrp1, AgeGrp2, AgeGrp3 ...so: for outcome measure 1 (L1) , at assessment (L2), for parent one (L3) who completes the AgeGrp1 (L4) measure there are 50 flelds. Each age group has 50 (or so) fields (slightly different for each age group as might be expected) and each respondent also has slightly different measures for each age group (so the three age groups for self, parents, teachers etc are slightly different also (as also might be expected). Now: That means for OM1at assessment for Self there are 3 x 50 fields =150 fields. Multiply that by the 6 L3 tabs and you have 900 fields under 1 timepoint tab. There are 30 timepoint tabs... And 8 OM tabs... (all vary in their number of fields, but all contain plenty) I have created all the fields (L3 and L4 tabs) under assessment. To create all the fields at timepoint 2 (which are essentially the same - all I need to do is duplicate the assessment table and rename it as Follow-up_1 and relate it to assessment via a UR number (and so on for all the timepoints, duplicating and renaming the table, simple...) I can also copy the layout structure under the assessment tab and paste it under Follow-up_1- but the prolem is (of course) all the fields are still assigned to the assessment table. Now if I paste the layout into the Follow-up_1 layout, then it is easier to re-assign the fields because you don't have to seach for the table when you double click a field, it is there at the top of the dialogue box and given the field name is the dame, then once the table is selected all that is required is to hit the Enter key and move to the next field). Once all the filoeds have been re-assigned I can copy and past the ab structure back into the correct timepoint. However, I can only do one field at a time... and with so many fields it will take forever to re-assign - hence my question: Is there a way I can can I re-assign all of them at once (to save all that to-and-fro...)? Now you are going to tell me about portals ... I know... but that will get messy too... (but I'll go that way if I have to, but if I can bulk re-assign, I won't have to Best regards, Rramjet. 3x50 flelds = 150 L4 fields. That is there are 8 otcome measure At each time point there are a number of outcome measure and each outcome measure contains between 30 - 50 fileds.

Sorry Comment - but you might have to explain that one in a little more detail for me... I mean, each time point will contain the 'same' 500 fields in related tables (time 1, time 2, etc) so that for one client the information gathered at time one is the same as that for time two (ie; follow-up data) - and I want to (say for client x) enter data into Time 2 (or other time points) by simply clicking on the time 2 tab (or other time point tabs - which contain fields from their respective time point tables) -. so please forgive me if I am being obtuse, but how does that relate to your 'indiviual record in a related table'? And how does that solution solve my reassignation of a single-field-at-a-time problem?

Hi all - haven't been in for a while, but new job - convinced them FileMaker was the way to go; so a new database to build... I have a tabbed layout with a number of levels (each contained under a top structure of tabs - Time 1, Time 2, Time 3, etc) containing a total of nearly 500 fields at time 1 (and drawn from a Time 1 table). As mentioned I then have multiple time points (30 in all) that contain the same information (just the next time point along). To create the different time point fields I can (for example) duplicate the Time 1 table (and rename it to Time 2 - it does not matter if the time 2 fields are the same name as time 1 - I can easily take care of the renaming when I export for analysis purposes using SPSS syntax) - and I can copy and paste the time 1 tab structure from the main Layout and place it into the Time 2 Layout (thinking that I could more easily re-assign the fields to the Time 2 table that way – because they are still all of the same name and position on the layout and in the table) - before copying and pasting that time 2 tab structure (with the reassigned fields) back into the main layout under the time 2 tab - and so on for each time point) - but as the fields are (of course) still assigned to the Time 1 table after the initial copy and paste - and with 500 fields - and with (seemingly having) only the ability to re-assign one field at a time to the Time 2 table – a great deal of time and RSI from all that clicking of the mouse looms (especially if I have to do all that 30 times over). So, is there an easy way to re-assign all the fields in bulk once they are on the correct layout before copying and pasting back into the main layout tab structure? Or is there a better approach altogether? Thanks and best regards, Rramjet

OMG... (bemusement at own stupidity ) ...so it was. Thank you Vaughan.

Usually, when I enter Layout Mode the whole screen (I have a widescreen display) has a white background with page and gridlines, etc displayed over the width (and depth) of the screen and I can utilse the whole of the screen to insert and manipulate fields/objects etc. However, on one particular dbase file, in L/O mode, the layout seems to restrict to just the parts and the rest of the screen background is blank grey - with no gridlines etc. Objects and fields do appear in this grey screen area (to the right of the L/O parts) but they cannot be dragged or dropped, only manipulated using the keyboard (see top picture attached). Draft versions of this file display in the normal way in L/O mode (see bottom picture attached), so I presume it is something that the users have done to it…. or not … But for the life of me I cannot figure out what is going on or how to revert to a “normal” display. Any help would be appreciated.

Final update: Master Table (Client demog. etc info, SRN) Program Table (linked to Master by SRN) Session table (Linked to Program table by SRN - shows date and type of all contacts made in a specific program and shown on Program Layout via a portal) Report table (linked to second instances of all Sessions tables via "x" - cartesian relationship) In Sessions table create all the totals variables needed. In my case each is conditional on a Month and Year and Activity type (of session) Activity_1_Duration (calculation) Case(Month = Report::Month_Select and Year = Report::Year_Select and Activity_type=1; Duration) Activity_1_Duration_Total (summary) Total of Activity_1_Duration In the Report table construct calculation fields that pick up the Totals fields from the sessions table There are no records in the Report table and all options for creating and deleting via the relationship are turned off (left blank) A script is triggered (Set Script Triggers... > OnObjectModify)set on both the Month_Select and Year_Select fields on the Report layout (both fields have a dropdown list: month numbers and years) Delete All records [No dialog] New record/Request Omit Record Show Omitted Only This is important because FMP seems to need this "toggling" of the new record to get the selection fields operationalised back in the Sessions tables and also so that the new record (in the Report table registers the changes made after the selection variables have been selected and operationalised in all the different tables. The delete all records is necessary because every record created "remembers" its own selection and totals fields and cannot be changed via the selection variables(this might be useful for some - but I find it confusing). Anyway, this all means I can have a complex report table (with as many rows and columns of any differing calculable nature as I want - no need to sort by anything), drawing information from lots of different databse tables with different clients and session info, all in the one place. The only downside is that the larger and more complex the report table, the longer it takes FMP to calculate the result... can't have everything I suppose... : Hope this might be useful for others. Regards Rramjet

...stranger and stranger... If I affect a change in the sessions table...and then go back to the "precursor table (which has 0 records) and create a new record and then toggle the green "Found" button in the FMP toolbar... then the new record then reflects the changes made... Perhaps I will have to script this somehow... Delete the old > create the new > show the new... EDIT: interestingly, if I change the selection field before I delete the old, then I create new, then toggle the "Found" button, this seems to force the selection fields to operate properly back in the sessions tables...

Okay, I am part way there... From: http://fmforums.com/forum/showtopic.php?tid/213200/ I get: "Build a cartesian relationship from Table C to Table A using the "x" relationship which allows Table C to "see" all records in Table A." That works... (my "precursor" table now has a cartesian relation between all the different Program Session tables. This requires of course that another instance of each session table is created but I can then create a calculation field in the "precursor" that = the session field (Total_N_by_Month_and_Year or Total_Duration...) in the Session_2 (cartesian) tables. (remember the Total_N...were themselves summary calcs in the session tables drawing from If(Month=month_select and Year = year select; N; 0))... EDIT: Actually, it works only in the first instance...but then won't update to reflect some changes in the sessions table (the problem seems to be the selection variables - adding and deleting records DOES affect change that is then reflected in the "precursor" table... but when a change is affected via the selection variables, that change in totals is NOT reflected in the precursor table...hmmm...) Now I can display all the different Program Session totals in the one file (which is related directly to the Master table of clients via a SN and by cartesian to second instances of session tables) Hopefully I can now summarise those fields in this table... I still have the problem of the "selection" fields, when changed in the "precursor" table (and picked up in the Session tables) not affecting the required change in the sessions tables... I still have to go individually to the sessions layouts to change them there. A workaround could be to have a copy/paste script that copy/pastes the selection field info into the various session layouts...but that's clunky...must be a better way...

I am not sure if I can explain this well enough but here goes... I have a database where a number of clients are recorded in a Master table. Each client can participate in a number of different programs (for which a child Program table is created). Each client can then have a number of separate contacts with the company in each program, (which are recorded in a Sessions table via a portal in the Program Layout). Thus one client could for example have 10 sessions in Program A and 4 sessions in Program B (each on a different Date). Each session also records the Duration (mins) for that session. I want to be able to summarise the number of sessions over a specific set (group) of programs by month and year and display the information in a table. Across the top of the table the column headers read: For the year xxxx - Monthly Total Sessions; Monthly Average no. Sessions; Year-to-date Total Sessions; Monthly Total Duration; Monthly Average Duration; Year-to-date Total Duration Down the left side the rows titles read: Green (ie; summary of Programs A, B & C totals) Red (ie; summary of Programs D & E totals) Blue (ie; Program F totals) etc... (there are 15 of them in all) Now... GetSummary only works if the parts are sorted according to the specific break field so that simply does not work. However, In a specific Program Session Layout I can create Month(Date) and Year(Date) fields in order to indicate the Month and Year fro the date of the session. I then create both a "Month_Select" and a "Year_Select" field so that I can set these to the specific month and year the data is required for - and thus I can calculate: Number_of_Sessions [calculation field] = If(Month=Month_Select and Year=Year_Select; Count(Session_ID); 0) {I could also use the Case function} and Number_of_Sessions_Total [summary field] = Total of N_Sessions and this gives me a month and year subtotal according what I enter into the Month_ and Year Select fields. I can then use the same logic to create total Duration. Now the problems begin. I can display these totals on a Sessions layout specific to each program (drawing the information directly from the Sessions table for each). However, if i want to diplay them all on a single layout (as a precursor to constructing the above table) it simply does not work. Not only that, if in the "precursor" layout I create Year_Select and Month_Select fields (for that is where I want to be able to "select" this information) and pick them up by calculation back in the sessions table, the "select" fields change (back in the sessions layouts), but the totals no longer change accordingly (as they did when I "selected" directly in the sessions table layout - even when I direct the above calcualtions to get the Month_ and Year Select information from that layout). Now a "way around" this could be to create the "precursor" layout/table full of global fields and a script that goes to each of sessions layouts and in turn copies then pastes the summary totals back into the global fields. But two problems arise: 1. I cannot get the copy/paste functionality to work if there is no layout present (ie; I cannot simply point to a table and copy select/paste select the relevent field if a corresponding layout with the fields is not extant - and if a layout IS present then I have to use Go to layout 1 > Copy field > Go to layout 2 > Paste field). (I cannot get Set field to work...) 2. I cannot get the Year_ and Month_Select variable to affect a change in the Sessions totals layout fields from the "precursor" layout anyway... (the sessions Month_ and Year_ Select fields pick up the information change okay, but the summary totals do not reflect the change...) I am frying my brains over this one. In the past I have worked around this problem by creating individual month fields for each category of summary data I want (and in one case ended up with over 1400 fields - plus a copy and paste script to boot - plus all the extra layouts to operationalise this procedure...) ...but I do NOT want to repeat that process ever again... there must be an easier way to do it surely? ..probably missing something really simple (again) but any suggestion would be greatly appreciated... Best regards, Rramjet

I am not sure I understand your intentions here. I was talking about a NEW STANDARD layout containing just a Header and a Body (Layouts > New layout/Report > Standard Form... and follow the prompts...) and at the end delete the Footer and arrange the fields in a row in the Body - closing the Body up to show/contain just/only that row of fields - and place the field names into the Header just above the respective row of fields - editing them to plain English. Then assign Sort by functionality to the Header names. Resize the Header so you can add a title, navigation, function, etc as you wish. Then to your original layout add a button that takes you to the NEW Layout and in the NEW layout add a button that takes you back to your original Layout.You then don't have to mess around with the various "views" in FMP... (I have also found it useful to actually script the navigation buttons between the two layouts to pick up and display the current record client going in and Show All going out. I also include a script on a client name in the NEW "LIST" layout so that clicking on the name takes you to the individual record in the original layout.

A bit more work, but... What about creating a new layout (a Header and a Body) with the required fields in a row in the Body (as in Table View) and the column names (as text boxes) in the Header directly above the fields. You can then assign sort by functions to each of the column names. The advantage of this is that you can also place navigation buttons, function buttons, Record number displays, etc in the header, giving you all the functionality of a Table View with added bonuses. I have done this and it works really well (users love it even more than the standard Table View). I also include a Checkbox field (value list "1") and Mark, Find and Clear Checkbox (scripted) buttons in the header above the Checkbox field.

Okayyy... I have now found the answer why! but not the solution... I ALSO had some self-join tables (Sessions 2, Sessions 3, etc) to enable the production of reports. I had related these tables by the field required in the reports (like "Location"). Because I had 3 self join tables, and each had a different field for the self-join relationship, this covered all the combinations in the original sessions table. I think what happened is when I deleted Portal Row, the relationships meant that everything "related" got deleted also... meaning ALL the records... (so Bruce ...you WERE precisely on the right track with your suggestion) I then created a complex self join by including the Session_ID in the self-join relationship with the field required to produce the reports and presto! The DeletePortalRow button works to delete only the records with a particular Session_ID... of course this means the first session from ALL Session table records also disappear...but it is a step in the right direction and tells me WHY it is happening... Now I gotta work out how I can have the reports (and the self-join tables) and delete ONLY the record in question... perhaps if I could add the Client_ID from the parent table into that relationship, logically that would constrain it...but I don't think FMP will allow that... A work in progress obviously... but at least we now know WHY the DeletePortalRow command was behaving as it did... Okayyy (again)! I picked up the client ID in the Sessions table and added it to the self join relationship and bingo...! Now the DeletePortalRow button deletes ONLY that session for that client. Just what I wanted! Phew... probloem solved. Bruce, you were right on the money...(and by-the-by) so were the original posts I referenced as they also remionded me to look toward the "Delete related records" section of the relationship... but it was just not THAT relationship that was causing the problems...it was the subsequent self-joins that did it (and I had completely overlooked them ... my database is relatively complex, with over 60 tables and multiple self-joins hanging off them all over the place so that one simply cannot see the whole database on one screen in the relationships/table view - and in that Morass, I simply missed the critical self-join tables. A salutary lesson for me - Don't forget about ANY of the relationships you create, they just may come back to haunt you when you least expect it (and in ways that you never dreamt of when you created the relationship)! Well, apologies if I have wasted anyone's time. BruceR gave me the key...I just had to find the lock it fitted! Best to all, Rramjet.

Hi Bruce - Yes... Each Session table is related to it's parent via a serial number with "Allow creation...", "Delete related records...", and "Sort records ..." checked on the Sessions side but nothing on the parent side. This holds true for about 15 sets of parent/session details table pairs (the parent table in each case is also related back to a Master table via the "same" serial number). Each sessions table portal row has a delete button inside it. Each of these buttons, when clicked, deletes the portal row in which the button was clicked (this means it deletes the sessions table record that is related to the portal row - but critically leaves all other related records (the other sessions for the same particular client) in the sessions table, alone). Only in one portal (of 15 that do work correctly) does the DeletePortalRow button delete EVERY record (related or not) in the related sessions table). Considering it all works fine for all the other portals... and nothing is different in the relationships between parent and sessions (child) tables for any of them (including the misbehaving one) could it be something to do with the layout of the fields in the portal row? (although this seems unlikely, that is the only difference I can see). Perplexed Rramjet ...Oh...I just realised another difference ...In Portal Setup... I unchecked the Sort by Session ID so I could insert the sort by Date button into the portal... but I tried removing the button and turning the Portal Setup Sort by back on ...and it still deleted ALL session records... huh... still perplexed...

I have a number of portals, each with a button in the portal row linked directly to the DeletePortalRow command. Each of these these portals represent the Session Details (in a child table) for a particular client (registered in the Master table). All the DeletePortalRow buttons work fine (they delete only the portal row that the button is clicked in) except in one particular portal... When I click on the DeletePortalRow button - it deletes EVERY record (related or not) from the child (sessions) table. A scripted DeletePortalRow behaves in exactly the same manner. The only difference (that I can see) is that this particular portal has more than one row of data (3 actually). It also has some text labels for the fields and a couple of different buttons (Sort by Date, GotoPR First & GotoPR Last). I have looked at posts: http://fmforums.com/forum/showtopic.php?tid/209568/ http://fmforums.com/forum/showtopic.php?tid/202374/ http://fmforums.com/forum/showtopic.php?tid/201204/ but although seemingly related, they don't give me any clues. All I can think is that it must be related to more than one row of data in the portal row ... has anyone else come across this problem? Any suggestions welcome. Regards, Rramjet.

Yep, that's where it is in FMP10 too! Huh... Sometimes the simplest things... Thanks a million.

I want to reposition the layout part handle so it is vertical rather than horizontal (so that it does not cover elements in the header that I want to work with). I know it can be done (I have done it before myself!) but I have lost/forgotten the way to do it. It is obviously a simple matter - if not I would have made a note of how to do it when I did it before (yes, silly me...). Help please..? Regards, Rramjet.

Hi Bruce - Yes, the Show All step was a mistake and does indeed defeat the find. The Set Error Capture is a useful step (I was tossing up between reinforcing to the users the find was unsuccessful by allowing the dialogue to appear, but considering my goal of minimising the number of "clicks" a user has to perform I will include the step). Thanks you. I will re-examine the necessity of the all the "Exit Script" steps... However, your current formulation cannot be operationalised because the If > If > Else > End If > Else If > End If structure throws up an "Invalid Script Step" at the Else If stage (missing an "End If" in the middle there) - but after including it something strange happens when a record is not found (after searching for a surname not in the database) and a new one is created... FMP tells me that even though it looks like a new record is created, it does not actually exist... I have to manually sort the records (by twice (!) clicking on the (now) greyed out FMP Total/Found records indicator button) before it appears... weird ...and then of course it is lost in the pack...). Anyway thanks for taking the time to put forward your suggestions, they have definitely provided me some useful tips (and something to think about as to why your solution behaves in the weird manner it does Best regards, Rramjet

Okay - I apologise again. I totally understand the eye-rolling. I was naive and hasty in my use of terminology. I will try to be more thoughtful when posting in future. Rramjet.

Solution Found Not sure if all steps necessary - no time to clean it up just now... It works and that is the main thing. Working from Comment's suggested script steps and my original I get: Enter Find Mode [] Show Custom Dialogue ["New Record"; (text instruction)] If[Get(LastMessageChoice) =1] If[isEmpty (Surname)] Enter Browse Mode New Record/Request Exit Script Else Perform Find[] If[Get(FoundCount)=0] Enter Browse Mode New Record/Request Exit Script End If End If Else If [Get(LastMessageChoice) = 2] Enter Browse Mode Exit Script [] End If Show All Records Appreciate your scripting suggesrions Comment and BruceR. Best regards, Rramjet

Wow... who did I offend? The terse and condescending replies here indicate that I must indeed have committed a grave offense. I sincerely apologise if I have. I did not mean to offend in any way at all. Thank you for the above suggested solutions, I will experiment and get back to you as to their applicability in this case. Rramjet

Ummm.... but the script works perfectly... and the IsValid() function is the precise function for the job because if the find request returns a valid field (even an empty one if that is the criteria searched for), then the script exits, it's as simple as that. Hmmm ...so the find request with empty criteria will produce an error - but that's the precise problem here. Nevertheless you have made me realise that it is perhaps an insurmountable one. I just wish (in this instance) there was a way where a field simply left empty would search for an empty field - and not finding one would create a new record instead (according to the parameters of some script step(s) that I could insert in the original script). However, I was in error in assuming a bug. I realise now it is in fact a FMP "feature" (that a find request with empty criteria will produce an error) that in this case prevents me accomplishing something I want. I can see why specifying = to find an empty field would be the way it is done in FMP, but simply leaving the field empty could be made to accomplish the same thing surely...? ...or perhaps I am simply asking the impossible. Anyway, thanks for the reply BruceR. I appreciate it. Rramjet.

Hmmm.... Given the resounding lack of response here, can I assume that it is a genuine bug in FMP10 - and that no solution is possible?