Something similar to a for each loop for fields in a table?

[mdavis6537]

Hello,

 

I am new to FMP and learning the script steps. What I am looking to do is go through each field in one of my tables and if it equals 1  then I want to add a record to a seperate table and copy over some data. Most of these fields are checkboxes, so making them equal 1 was easy enough.

 

To give more detail - I have a customer table. Each customer has a room(table), and each room has a ScopeSheet(another table. The scope sheet is basically a form with a bunch of checkbox fields. These scope forms are filled out, one for each room. All these tables are related through relationships with keys. 

 

What I want to do essentially, is go through each field in the scope sheet for a room, see if the field was changed (or if it equals 1) and if it was, put it on a report.

 

As I have been going through the FTS, my approach to the above has been this:

 

Set up a new table called "Changed Fields" with a foreign key linked to the Scope form. So that each scope form can have one report, but a report can have come from multiple scope forms.

 

Set up a new script - the script it launched from a layout that is linked to the customer table.

 

You can see the script attached, but basically my idea was:

 

•  save the primary key of the scopeOfWork record into a variable
•  go-to my Report Layout - linked to Changed Fields table "currently called scope report"
• Loop (currently set to 10 for deubbing purposes) through the fields of my scopeOfWork Record
• if the field is empty, go to the next field
• Otherwise add a new record to the scope-report table - keeping the name from the field in the Scope Of Work

 

I am just completely lost on how to finalize this. My main points of confusion are this:

 

• Am I even on the right track with how to go about this? Is there a way simpler option?
• After I create the new record over on my ScopeReport, how can I get back to the same spot where I was on my other table? How can I even make sure I am going back to the same record of that same table - and then get to the next field from where I left off?
• When it comes to "Navigation and Context" I am just lost. I understand that the current layout is what sets what table you are working with. But I have a hard time connecting which record I am working with when designing a script. How to know if I have the right record "selected" and how to get back to that record, etc. Its all just a bit confusing to me I guess.

 

I know this is a complicated question so anyone who is willing to try and help me get on the right path I REALLY appreciate any help you can provide. Thanks.

[comment]

Could you clarify a few points?

 

I have a customer table. Each customer has a room(table), and each room has a ScopeSheet(another table.

 

Can a customer have more than one room? Can a room have more than one scope sheet?

 

 

What I want to do essentially, is go through each field in the scope sheet for a room, see if the field was changed (or if it equals 1) and if it was, put it on a report.

 

Changed compared to what?

Is it important to keep a record of these reports?

 

 

It sounds like the "bunch of checkbox fields" should really be a bunch of related records in another table. In such arrangement, reporting the records that are checked becomes rather trivial. In fact, you should not even have records that are NOT checked.

[mdavis6537]

First thanks very much for replying - I am happy to clarify:

 

Can a customer have more than one room? Can a room have more than one scope sheet?

 

A customer can and most definitely will have multiple rooms. A room will only ever have one scope sheet.

 

Changed compared to what?

- Basically on 90% of the fields, all I care about is "Was the checkbox checked, or was it not checked". There are fields where a user might input a value such as a number. 

 

 

Is it important to keep a record of these reports?

Yes - they will need to be stored and be able to access them later.

 

It sounds like the "bunch of checkbox fields" should really be a bunch of related records in another table. In such arrangement, reporting the records that are checked becomes rather trivial. In fact, you should not even have records that are NOT checked.

 

Just to clarify - what I am looking to get on the report is only the fields that were "checkboxed" or marked, whatever you want to call it. Only the altered fields, not all the fields that are available. If you mean I should put the fields that are altered into a seperate table, that was kind of what I was trying to do - but I guess I need some real "tell me like im 5" help in figuring out how to get them over there, and not add the fields that were not changed.

 

Again thank you so much for your help - I have literally been trying ot figure this out for days.

[comment]

I wish I understood better what this "scope sheet" represents in real life. It sounds like some kind of a progress report, where eventually you'll end up with all of the fields checked. If that's the case, it would be better to use an actual date to mark the completion of a [stage?/task?] rather than just a tick.

 

If you mean I should put the fields that are altered into a seperate table, that was kind of what I was trying to do

No, what I meant was that instead of having say, 15 fields in the ScopeSheet table, you should have (up to) 15 related records in another table. Actually, since you say a room will only ever have one scope sheet, the ScopeSheet table is redundant - but that's a minor point. The important thing is to have a table where each scope sheet "item" is a separate record.

You will also need another table, with 15 permanent records, to hold the item labels. Again, I wish I could use more meaningful names; in the above example of a progress report, this table could be called Steps. Then the other table, the one marking the actual progress, would have these fields:

• RoomID
• StepID
• CompletedDate

In this arrangement, you would not even need to store the progress reports, since you can always generate a report for any date just by finding the items that were completed on or before a given date.

[mdavis6537]

Let me try to provide more information:

 

Essentially this is a form that is filled out by restoration technicians. They might go into a house of a customer who has had a water damage and they have to fill out a ton of information. For example, one section on the form asks for flooring that was damaged, so I have a heading: "Flooring" and then checkboxes: Hardwood, laminate, carpet, etc.

 

Back at the office, this data has to be input and sent to the insurance company. But the insurance company obviously doesnt care about the unchecked fields for hardwood or laminate, they just need to know about the carpet that was damaged.

 

I am thinking as I type this out - I guess instead of having seperate fields for the various "checkboxes", I could make one field called flooring and make it a list. The thing is some of these fields only have one option. Like "Remove Stove?" - its a checkbox on their current paper form. I suppose I could make that a list also and have an option for none.

 

The whole purpose is to make this simple and easy for the techs, that is why I originally went the check box route - the more complicated it is the less likely they are to use it. The other thing is there is literally hundreds of these fields, and a lot of them wont even be used for a job. So I really dont need to show those on the report because they dont even matter. Even showing them as "none" is really a waste for the person in the office to have to read through. It would save them so much time to only show the fields that the tech marked on the job.

 

I hope that clarifies.

[mdavis6537]

I wish I understood better what this "scope sheet" represents in real life. It sounds like some kind of a progress report, where eventually you'll end up with all of the fields checked. If that's the case, it would be better to use an actual date to mark the completion of a [stage?/task?] rather than just a tick.

 

No, what I meant was that instead of having say, 15 fields in the ScopeSheet table, you should have (up to) 15 related records in another table. Actually, since you say a room will only ever have one scope sheet, the ScopeSheet table is redundant - but that's a minor point. The important thing is to have a table where each scope sheet "item" is a separate record.

You will also need another table, with 15 permanent records, to hold the item labels. Again, I wish I could use more meaningful names; in the above example of a progress report, this table could be called Steps. Then the other table, the one marking the actual progress, would have these fields:

• RoomID

• StepID

• CompletedDate

In this arrangement, you would not even need to store the progress reports, since you can always generate a report for any date just by finding the items that were completed on or before a given date.

 

 

Reading through your answer again - this sounds exactly what I have been trying to do in my latest hack at this. However, instead of a date field, I need to just have a name field, which would be the name of the "item" that was marked. What I cant figure out is, as I create these records in the new table, how can I set the name field to be the same as the field in the Scope table that was "checked"?

[comment]

Sorry, I am afraid you have lost me there. In my suggested method, there is no 'field in the Scope table that was "checked"'. You click an item "label" and have a script create a record that is related both to the current room and to the clicked label.

 

Alternatively, you could create the related records via a relationship by entering data. However, this is more suitable for entering notes than just marking.

 

See also:

http://fmforums.com/forum/topic/31495-portal-pre-population-with-records/#entry142456

 

 

---

And also:

http://fmforums.com/forum/topic/71739-copy-data-in-one-table-paste-in-record-of-another/#entry339358

Edited November 27, 2013 by comment

[mdavis6537]

Thanks again for all your help. By re-arranging my tables and using portals, I was able to get this figured out for the most part. 

 

My final question, if you would be so kind, is: Is it possible to alter the fields that are displayed in a portal after the portal has been created? Preferably by a calculation?

 

Essentially I would like to run a calculation (isEmpty(Get(ActiveFieldContents)) for example)  for each field in a table that is being displayed via portal, and if its empty dont show the field, otherwise show it in the portal - is that possible?

[comment]

I *think* what you are really asking is how to remove records where some field is empty from the portal? At least I hope so. Otherwise you still (or again) have a structural problem. 

 

Just about any time you say "I need to do something for each field" it is an indication that you are using fields instead of records.