Clearing fields

[newtothegame]

I'm creating an FM file that contains multiple occurrences within one record where the input of different staff member names from a custom value list performs a look up to a "ref no" field for each "staff member" field. Fields indicating a "time from" and a "time to" for each "staff member" field are then manually inputted.

In this application, there could be times where the user might decide to delete the entry in one or more of the "staff member" fields in a record and I would really like, just to save the user having to manually do it, have FM automatically remove the data from the three other fields.

Although new to FM, I've worked with database applications in the past that employed a "Clear" calculation function that empties the contents of one or more fields in a record, often but not necessarily depending on a result of an "If" statement. This would easily accomplish my intention here but I can't see anything within FM that appears to do the job.

Can anyone offer a clue? Forgive me if it turns out to be a dumb q with an obvious answer as I've only been working with FM for a few days.

[Lnstrq]

Not sure which version of FM you have - I have FM 9 and inside the scripts dialog box I do see a "clear" which is located under the heading "EDITING" - Hope this helps.

[bcooney]

Welcome to the forums.

"FM file that contains multiple occurrences within one record"

What does that mean? It suggests that you are using a field set to more than one repetition. This is not the best practice. From your description, you need a related "child" table of staff members to this parent record. Then, you'd show the related child recs in a portal on the parent form.

Rather than clear fields, you would delete the portal row.

However, in a script, you could simply use Set Field (myField, "") to clear a field.

You can create a button and assign in a one step script step of Set Field (myField; ""). Set field does not require the field on the layout.

If you describe your structure in more detail, we can help you build a solid foundation.

[newtothegame]

Thanks for the welcome and the suggestions and sorry if I appeared rude in not replying before this. I've been away and offline for a week.

The 'Set Fields to ""' suggestion works somewhat satisfactorily if I set the script to trigger within the layout at "Record Commit", as the users need to exit the record for other calculations to be performed correctly and they will therefore be instructed accordingly.

However, as mentioned in my previous post, the old database software that I used in years past featured a "Clear" function as one of its functions for field programming, as opposed to a script/macro, which had the ability to remove the contents of one or several of any fields in a layout/record, with or without the qualification of an 'If' or other condition.

For example, if you had a field called "Surname" and you wanted the fields "First Name", "Title" and "Date of Birth" to be emptied of their contents if, for any reason, the data in "Surname" was removed, the programming (within any of these fields) could include a command such as 'If Surname="";Clear(First Name; Title; Date of Birth)'.

While I understand the syntax in FM differs to what I've just illustrated, some sort of similar "Clear" function for programming of fields, rather than within scripts, would be far simpler and quicker to encode. The data would also disappear as soon the "Surname" field was emptied, rather than on record exit, as well. (I tried using the script trigger as 'OnLayoutKeystroke' but this completely prevented the data from being removed from the Staff Member Name field for some reason, which was totally NOT what I wanted.

The reason for my use of multiple instances of the Staff Member Name within the one record is that's what's required by the users for the layout which I've developed. There is one record of 14 days of shifts for each client. Each day heads a column containing 8 work periods which in turn each include a Staff Member Name, a start time and a finish time field.

Different staff members can work different work periods within a day for various clients and the users who will enter the data want to see the form spread over the 14 days for each client.

They don't want portals etc - just a standard form that looks like a table, with columns for the dates/day of week containing the categories and fields mentioned above.

Table view doesn't work because of the complexity created by the work period sub categories but I do agree that what I've done may not appear to be best practice. For instance, I had to create 112 virtual relational tables just to accommodate the lookups required to get staff member number data from a Staff database after the names have been entered!

But it's in the format that the girls want and what the girls want is what they get!

[bcooney]

You're building a complex system, and I believe the structure that you are taking will prevent you from reporting your data (when that need arrives).

Two systems came to mind that I believe will help you. Take a look at:

http://www.seedcode.com/cp-app/ste_cat/sccomplete11

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

Also, search for "booking" in the forums.

[comment]

They don't want portals etc

It's not the users' concern HOW the solution works. What you describe SHOULD be accomplished using portals.

The reason for my use of multiple instances of the Staff Member Name within the one record is that's what's required by the users for the layout which I've developed.

There is no reason why the layout you describe couldn't be built with portals - and the users wouldn't know the difference. You should not compromise proper data structure to accommodate interface requirements. What you are trying to build is complex enough when built properly; if you start with one hand tied behind your back, it's going to grow into an unmanageable monster (112 relationships being a prime example).

I would suggest you start here:

http://fmforums.com/forum/showtopic.php?tid/176396/

[bcooney]

@comment - So many great contributors on that thread. Wish they'd come back...

[comment]

Indeed.

[newtothegame]

Once again, thanks, bcooney and comment for your time and care in responding to my questions. This mean old world can’t be all that bad if there are angels like you with your level of expertise who are willing to trawl through forums like this with no other reason than to help relative novices like me.

It seems that we’ve steered away from my original question about a “clear’ command within field programming but that may be a good thing as you are both pointing me towards something which, as an old time user/programmer from the pre-relational days, I’m really having trouble getting my head around. That is the use – as opposed to the creation – of portals.

I’ll get to the details of my quandary by first addressing some of your comments:

bcooney- “You're building a complex system, and I believe the structure that you are taking will prevent you from reporting your data (when that need arrives).”

It turns out that the data entry layout, which is set up to view as a standard form, is also the actual report of the data for this dbase that the ultimate user will take away in their briefcase, and it’s set up to print out in just two pages, one page per week, so there’s no problem with the reporting. It has also turned out that the Staff Ref No is not required on this layout. The Staff Member Name is accessed from a drop down value list from the related “Staff” dbase. The Start and Finish Times are new data which need to be entered manually into appropriate fields under each Staff Member name.

The Staff Ref No IS however still required in another hidden layout which is used to create another related staff roster file. I have all of this working perfectly except for an issue of page breaks in the report which is auto-created in the staff roster, which I think I should cover in a separate thread, as I’m sure it’s a bug in the FMP software, (having tested it enough). (See the link to the tread in my next post, below).

Now - to portals:

comment, I took your comments on board, in particular about the data structure and using portals vs the 112 relationships, which I agree were very cumbersome and time consuming to create (in the old days I would have achieved the same with five xlookup statements in a couple of minutes instead of the hour or so it took for the 112 relationships).

Given what I said in the previous two paragraphs, I don’t think the data entry layout will require the use of portals (please correct me if I’m wrong). However it appears from your comments that it may be far better practice to use portals in the hidden layout.

I’ve learnt how to create a portal and I threw one into the hidden layout to see what it would do. Sure enough it displays the Staff Member Name and Staff Ref No field from the Staff dbase just as it should. But it only shows the one record based on the one out of 112 relationships to which each of the related Staff Member Name fields coincide. I should make it clear that each of the 112 Staff Member Name fields in each record in the data entry layout is actually a different field with a different field name, necessitated by the fact that different staff will work different shifts with each client, including in some cases, more than one staff member at the same time.

Now to the dumb question: if portal(s) will improve the data structure, and simplify/streamline everything, then what do I do with it/them now? To get around using the 112 relationships to pull out the Staff Ref No, how do I relate the various 1 up to 112 different Staff Member Names from the data entry layout to the Ref Nos generated by a portal? Is there some way to lookup directly to the portal or to pull the correct Staff Ref No out of the portal to use in the other programming that I’ve used to auto generate the staff roster data? It appears to me that I’m going to have to make 112 portals using each of the 112 relationships I’ve already created which is self defeating.

Sorry once again for my lack of understanding of the use of a portal as a newcomer, but all I can see from FM’s help and user guides is how to create the portal and that it displays data from the related table. Simply displaying data is of no help. What I need is one single portal to actually function as a lookup table for all Staff Names/Nos or else it’s of no use to me. I may as well just stick with the 112 relationships which despite being a pain to create, as mentioned before, are working fine.

Edited November 29, 2010 by Guest

[newtothegame]

FYI, in case anyone's interested, here is the link to the new thread I created re the page break issue I mentioned in my previous post:

http://fmforums.com/forum/showtopic.php?fid/31/tid/217930/pid/371761/post/371761/#371761

[bcooney]

"Now to the dumb question: if portal(s) will improve the data structure, and simplify/streamline everything, then what do I do with it/them now?"

Have you looked at any of the examples we have provided?

[newtothegame]

bcooney, I looked at all of your and comment's suggestions, thanks, and while interesting, they do not address my intentions, nor do they answer my dumb question.

Once again and despite what was said before about about the user's concern about how it works, I have been given a task that involves, at its outset, the creation of a standard form exactly as I described previously, with, amongst other fields, two tables each containing 7 columns (one week) headed by a date and day of week, with up to 112 DIFFERENT staff names, and dependent start and finish times for each, contained within the two tables in the one record.

That is the format that the users want. That is what they will get. I can't tell them what they want. I have to give them what they want.

I have done that and the dependent programming is all working the way that it's supposed to, apart from the problem with the page breaks in the subsequent dbase (not relevant here)

Where I am dumb is that if I am supposedly to improve my practice and file structure, I simply can't seem to find out how to get information out of a portal. I can create one and display one record per relation and that's it. But if that's all a portal can offer, then it's going to be more cumbersome than what I have already done.

I really don't want to waste any more of your or others' time and expertise if there's something that everyone else in the world knows that I don't about portals. I just can't seem to find out from the literature FMP provides, nor from forums such as this one or anywhere else, a simple explanation (not complex examples) of the actual usage (as opposed to creation) of a portal.

I'm normally a pretty intelligent person, but this one has me beat. In the old days, what I would have used that would have been equivalent to a portal would have been a simple lookup table and I'm blowed if I know why these relational dbases don't use them. I could achieve with a couple of lines of code what seems to take ages with these apparently improved dbase programmes.

What I need is a copy of the apparently as yet unpublished book, "Portals for Idiots". Until I get my hands on it, or a basic explanation on how to construct a portal based on multiple relationships to a single field and how to use the data from within that portal elsewhere in the dbase in which it's contained, I think I'll have to continue bumbling through my FMP experience without portals.

By the way, I only personally know of two other people who use FMP, both of whom have been using FMP for several years as opposed to a couple of weeks like myself, and neither of them can get their head around the usefulness of a portal either. Maybe this indicates there IS a need for someone to spell it out as it points towards there being plenty of others with the same problem.

[comment]

It's not about portals - it's about relationships. Let me take a simpler example to demonstrate the difference:

Suppose you have a parking lot with 14 spaces. You also have 14 employees - and you need to assign one parking slot to each employee.

Your method (I think):

A Parking table with one record and 14 fields for slots (or a repeating field with 14 repetitions). Each slot gets filled with one name from the Employees table (which has 14 records).

Problem:

An employee has left their car's headlights on. We want to locate this employee's record.

Now, if you have 14 fields for slots, and 14 relationships to the Employees table, you can click on the slot field and using Go to Related Record[] you will be taken to the correct record in the Employees table, where their phone extension can be found. I am not sure how you would do this with a repeating field for slots.

My method:

A Slots table with 14 records. Each record holds ONE slot number and (up to) ONE EmployeeID. There is ONE relationship linking Slots and Employees:

Slots::EmployeeID = Employees::EmployeeID

Note that this won't change if we grow to 500 parking slots and 600 employees.

In order to display the slots to the users in a convenient manner, I will add another table of Lots (which for now will contain only one record) and one more relationship:

Lots::LotID = Slots::LotID

Using this relationship I will show all 14 slot records in a portal (or several portals) on the Lots layout, instead of using a list view of the Slots table.

Edited November 29, 2010 by Guest

[dansmith65]

I think Comment did a good job at explaining the advantages of using relationships/portals, but here is a sample file that demonstrates the use of portals.

Have a look and try to think of way's you could apply these principals to the database you are working on. Try to be open minded about the way the information is displayed, as it may not be what you want (or what your users want), but try to think of how it could work, and what it would look like, even if it's not for this project, but for a future one.

portals.zip

[Vaughan]

That is the format that the users want. That is what they will get. I can't tell them what they want. I have to give them what they want.

You are confusing the interface with the data structure. They need not be the same. Always get the data structure right, then work out an interface that is easy to use.

Find out what the users need to do and create a solution that meets this need. This usually has little correlation with what they "want".