You cannot dynamically select fields to appear in a portal.
You can, however, dynamically select what records appear in that portal. Thus, each variable could draw it's value from a different portal row.
Thus, for Disease A, the portal rows might show two rows with two fields:
temp [ ] --> brackets denote empty number field for specifying value to be used in calculation.
Kg [ ]
For Disease B
temp [ ]
cm [ ]
OK. So in detail would I have:
1. New table for variables.
2. 2 fields, 1 for VarName, 1 for VarValue and the number of rows would equal the number of different variables? Or for each different combination of variables or even each different equation, I would have a pair of fields, Eg Disease A: field 1 = VarName, field 2 = VarValue; Disease B: field 3 = VarName, field 4 = VarValue etc.
3. I'm not sure how this would hook into 'Evaluate'?
4. Lastly some of the variables (eg. cm and kg) are from another table & layout because they are used for other calculations; they wouldn't be specific to this calculation.
2. at least 4 fields, you also need a match field so that they can be associated with the correct equation. And an additional match field to match it to the correct record on your layout.
3. it's difficult to be specific given the current info available in this thread, but the variable name would be used to determine where in your equation expression you would substitute the associated value before it is evaluated. A script may be needed unless you use FileMaker Advanced to create and install a custom function for implementing the substitution process.
4. In which case, you would need some kind of relationship to link that data to the current record and this will definitely complicate the design of your solution.
1. Was it correct I need the variables in a separate table?
2. For eg. above the number of fields would be: pk&fk ID fields then 1 field for each equation? Eg. Fields 1+2 are IDs, Field 3 would have temp + kg as rows, Field 4 would have temp + cm as rows? Correct?
3. I do have FM Advanced: I assume when you mention using a custom 'function' for 'substituting', you mean substituting eg. Field 3 variables being substituted by Field 4 variables etc. What is this 'function' if it's not a script?
4. What's the gist of how I'd implement your comment in 4. in FM Advanced?
Would the above still work using 'Evaluate' to obtain calculations from a list of equations (http://forums.filemaker.com/posts/970a5a4d74).
1. with what I am suggesting, you need a related table of values with associated variable names.
2. almost, it's two fk fields, no pk field required, but it's a good idea to add one, that makes for 5 fields (and my answer to 3 & 4 suggest field #6). FIeld 3 would be text for the name of the variable and field 4 would be number for it's value.
3. I suggest looking up custom functions in FileMaker Help. When you define a custom function, it appears with all of your other functions (Such as Get, Length, etc.) when you set up a calculation. The advantage to a custom function is that you can define it to be a recursive function where you feed it a list of values (your variable names and their values) plus the equation text and it uses recursion to run the list (which can vary in length for different equations) subsituting a value in the list for a variable name in your equation text each time it recurses until it produces a modified equation line that can be evaluated with the evaluate funciton. You'd feed in inputs such as "grams ¶ 23 ¶ cm ¶ 5 " ; "grams * cm" and get back: "23 * 5".
4. Can't really say without knowing more about your database and how you need it to function, but somehow, fileMaker has to be able to access all the required data to accomplish this. If all your variable names and values where structured in a single table of related records. A calculation in that table can use List to combine the name and value in a single field: LIst ( VariableName ; VariableValue ) and then List in the Parent record can produce the list I show in 3. above where all variable names and values form a single list of values. The syntax would look like this:
Evaluate ( ProduceEquation ( List ( VariableTable::CombinedField ) ; EquationField ) )
And Evaluate could also be incorporated into the custom function so that the function call looks like:
EquationEavaluate ( List ( VariableTable::CombinedField ) ; EquationField ) )
But if some of the variable names and values are not in the related table, you'll need a more complex expression to pull everying into that list for the first parameter. It might be: List ( List ( VariableTable::CombinedField ) ; List ( OtherVariables::CombinedField ) )
Thanks for all the input. I'll ponder this for a couple of weeks!
Last record duplicated using a script you suggest. As mentioned, it works but it duplicated the last of all records, not necessarily the current patient's last record.
The 'specifiy calculation' syntact was: Let ( R = Get ( RecordNumber ) ; If ( R > 1 ; GetNthRecord ( Field_XYZ ; R - 1 ) ) )
Portal 1: Only a specific Patient's, Prescription records are shown.
Portal 2: I used your 'specify' button in Prescription portal 1 to pull up only those Records relating to that particular Prescription.
Patient, Prescription and Record tables each have auto-enteral ID serials. Or are you meaning by 'The Last function', a value within each of the portals?
I think you posted this comment to the wrong thread...
Last ( RelatedTable::Field1 ) returns the value of Field 1 for the last related record. This will be the last record in the portal if the portal is neither sorted nor filtered.
Let ( R = Get ( RecordNumber ) ; If ( R > 1 ; GetNthRecord ( Field_XYZ ; R - 1 ) ) )
will work correctly as an auto-enter calculation and it will copy from the preceding portal record's value, not the most recently created portal record in the entire table. See this demo: https://dl.dropboxusercontent.com/u/78737945/GetPrecedingPortalRecordDemo.fmp12
Eg. You're right, Let ( R = Get ( RecordNumber ) ; If ( R > 1 ; GetNthRecord ( Dose ; R - 1 ) ) ) appears to work. However, while the script 'New_menu' correctly adds records to the Menu portal the 'New_meal' script fails to add records to the Meals portal, but does add records to the Meals table. Please see the example [FM_test_DB] in the DropBox weblink:
When I select an right click that text, I get a 404 error (page not found). Rather than share the entire file, posting just the script you are using may be sufficient:
To post a script to the forum:
- You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
- You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
- If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
- If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
Screenshot of 2nd level portal (1st level portal script using same structure works fine) uploaded.
What layout are you on when the script is performed?
How is the Meal table occurrence related to the Meal per Menu table occurrence?
Layout = Person
Relationships as field (Table)
- __PersonID (Person) to _PersonID (menu)
- __MenuID (Menu) to _MenuID (Meal) and
- Menu_no (Person) to _PersonID (Meal per menu)
In all cases I have NOT check Allow creation/Delete records in order for the script to determine the action.
Script Deugger gets stuck at the last step 'Go to layout L_Person and is at that stage of the script on the Meal layout and shows that though a new meal record has been correctly added, it has not got a MenuID number.
Whether you select "allow creation..." or not will have no effect on how this script performs.
What I can deduce about your relationships
Meal per menu?-----?Person----<Menu------<Meal
Person::_PersonID = Menu::_PersonID
Menu::_MenuID = Meal::_MenuID
In both of these, primary key fields are on the left and foreign keys are on the right. BUT:
Person::Menu_no = Meal per Menu::_PersonID is confusingly named. I can't tell from the names used, how this is supposed to work but given the script you are using, it doesn't really matter..
Now let's look at your script: You've indicated that you are on a layout based on Person:
Set Variable [$ID: Value: Menu::_MenuID ]
This script if performed from a button in a portal to Menu will put the MenuID from that portal row in the variable. If the button is not inside the portal row, the value from the first related record in Menu--usually the first portal row--is copied to the variable.
Go to Layout ["Meal" ( Meal ) ]
This creates a new record in the Meal table. All fields will be empty except those, such as an auto-entered serial number will be empty. Thus, the new record is very unlikely to be linked to any record in any related table at this point. (You'd have to have a specific auto-enter calculation in place for it to be linked to anything at this point.)
Set Field [Meal per Menu::_MenuID ; $ID ]
This step refers to a different table occurrence (Meal per Menu) than that specified for the current layout (Meal). But the current record, being new and basically blank except for fields with auto-entered value is not linked to any record via the relationships from Meal to menu to person to Meal per menu and thus nothing happens except that an error code is returned.
I would guess that the step should read:
Set Field [Meal::_MenuID ; $ID ]