Hi all,

I am recreating the form below in FM:

http://wirenet.mcmaster.ca/indicators/wmi_form_v.html

each row in the table is a species of plant that the user can check off to confirm having sighted when doing research.

each species has a "U" value and a "T" value that are added to two running sums when the associated checkbox is checked. the running sums are then divided to give the WMI value at the bottom

Here is an example of the Java code for a particular row:

if (document.getElementById("ELAC").checked==true)

{

wmi_top+=12;

wmi_bottom+=3;

}

wmi_val=wmi_top/wmi_bottom;

In FM, I have two tables:

WMI - has columns Species, U-value and T-value

Calc - has columns Name, Lat, Long, Calculated Value and a field that shows a value list from the Species column in the WMI table as a checkbox set.

What I dont know how to do is:

Have a checked item add a value (eg 2) to one running sum and another value (eg 3) to another running sum

Make sure that unchecking a box will remove the associated values from the running sums

This was all easy to do in Java, but how can I do it in FM?

I'd probably use a calc field that relies on the checkbox. Something like, if (flag_checked=1, value, "") and then sum the calc field.

doesnt that have the limitation that i can only have one checkbox?

my checkbox list has 90 checkboxes, and the calculation depends on exactly which boxes are checked

I don't think you are approaching this correctly. Looking at your form, you should have (at least) three tables instead of just two: one for Species, one for Inspections (location x on date y by person z) and one for Occurrences (a join of Species and Inspections). Do a search for "survey" for more details.

i do not understand why i would need a third table, or how it would work.

my search for "survey" yielded only a few similar questions, but did not provide many answers so my apologies for re-asking:

Say i was to use your three tables Species, Inspections and Occurences, would the user have to enter an Inspections layout, enter their name, location and date, then switch to an Occurances layout and select their associated Inspection occurance and finally enter the species that they encountered?

If so, i don't understand how that would be implemented as far as selecting their Inspection and I also don't understand if i would have to make a field for each species and then the user would populate it with a 1 or 0 ?

i do not understand why i would need a third table

Because if you don't atomize your data, you won't be able to summarize it efficiently. If the finding of species A at location X is in the same record as the finding of species B at the same location, it will be very difficult to produce a report like:

Species A:

• Location X

• Location Y

Species B:

• Location X

• Location Z

• Location W

i don't understand how that would be implemented

There are two stages to implementing this: one is the data structure (tables, fields and relationships). The other is the user interface (layouts and scripts).

In terms of the data model, there is a table of Species, where each species of interest is a record. There is a table of Inspections, where the user would create a new record for each time they "fill a form". Finally, in the Occurrences table, there is a record for each finding of a species (no record is required for species not found).

For implementation of the interface, I suggest you look at the demo found here:

http://www.fmforums.com/forum/showpost.php?post/149069/

It's not perfect, but it provides a start.

Note:

This is not a simple project that I would recommend for a beginner to undertake. Expect it to take some time and effort.

i REALLY appreciate all of your help, i have managed to get it working quite well, as seen in the file below (it is 5mb so i couldn't attach it to the post)

http://wirenet.mcmaster.ca/focus/WMI.fp7

now my only problem is that i have to manually select each plant that was seen from a drop-down list, whereas i would prefer to have each new Inspection record trigger the creation of 95new Occurence records that are pre-populated with each species, which are then shown in a portal that allows me to simply check off whether or not that plant was seen.

any tips on how this could be done?

my gut tells me to make a script that would somehow create all 95 records and then cycle through them, filling in the inspection ID and the species, but i'm sure that there is a simpler way to do this that i have not thought of?

The idea is to show ALL species in a portal or list view, and create records in Occurrences for those found.

I'm having trouble wrapping my head around this.

should i insert a portal in the occurence layout, have a New Record button in Inspections that

Go to layout(occurences)

Go to portal row(first)

Show all records or something

and then... make a loop that somehow creates new records and populates two fields (recordID and species)?

I am afraid I cannot provide you with step-by-step instructions. I suggest you need to understand the demo I pointed to earlier before you can try this on your own.

Alternatively, you could try another method: when you create a new inspection, create also all of the possible related records in Occurrences. This can be done quite easily by importing from Species, then marking the imported with the newly created InspectionID.

I don't like this method too much on account of all the redundant records, but it is easier to implement.

ok well i think i deserve more credit than you are giving me as far as my ability to comprehend.

anyway, i have managed to successfully solve this problem and it now works flawlessly.

i created a numerical Species ID that is unique to each species, then all that i had to do was insert a "New Inspection" button that runs the following script:

New Record/Request

Go to Layout["Species"]

Set Variable[$$Species;Value:Get(TotalRecordCount)]

Go to Layout [original layout]

Go to Portal Row [First]

Set Field [Occurences::SpeciesID; $$Species]

Loop

Set Variable [$$Species; Value:$$Species-1]

Go to Portal Row [Next]

Set Field [Occurences::SpeciesID; $$Species]

Exit Loop If [$$Species=1]

End Loop

Commit Records/Request

Go to Portal Row [First]

Go to Field [inspections::Name]

what this does is count the number of species then create a new portal row for each species while subtracting 1 from a variable. the bit after the end of the loop is to make sure that the portal is sorted, the portal is scrolled to the top and then the cursor is in the name field.

and that was it, everything is working beautifully now, thanks a lot for your help guys!

i think i deserve more credit than you are giving me as far as my ability to comprehend.

I have zero knowledge regarding your abilities, so it's not my business to give or withhold credit. It's only because you rate your skill as 'Entry level' (not to mention your screen name) that I believe a warning is in order.

A potential flaw in your system: if at any point you delete a species, your SpeciesID's will not be consecutive.

great point, i will look into having a way or re-ordering the species should one be removed (although one would only ever be removed if it went extinct!)

re-ordering the species should one be removed

That would be a cure worse than the disease, I think. Consider instead:

New Record/Request

Set Variable [ $inspectionID ; Inspections::InspectionID ]

#

Go to Layout ["Species"]

Show All Records

#

Go to Layout ["Occurences"]

Import Records [ -- Species::SpeciesID -> Occurences::SpeciesID -- ]

Replace Field Contents [ Occurences::InspectionID ; $inspectionID ]

#

Go to Layout [original layout]