script for repeating field split import clone

[NewFMuser]

Can someone help me?

I have a repeating field (visitdate) that is related to another field (patient), and i want to make a report that is a master list of all the visits of each patient. How can i do that? please help? Thanks in advance.

[CobaltSky]

Hello NewFMuser,

If your repeating field really holds dates and is related to a field that holds patients (their names, one would assume) then the relationship will not be producing anything useful because the dates will not match any of the names.

That aside, the method that one would generally use to produce a list of related records/data woudl be either:

1. To set up a portal based on the relationship (which will display a list of all the related records) and place the required fields from the related table into it, or

2. Set up a script which uses to Go To Related Record {Show only related records] command to isolate the related records on a layout based on the related table.

In either case, the resulting data set can be viewed on screen or printed.

[bruceR]

But the primary thing you should be doing is get rid of the repeats. They are a problem area for new users and this is EXACTLY the kind of problem they create. You can't report on repeats.

[NewFMuser]

hello ray, thanks for the reply.... sorry for this stupid question, but what is a "portal"?

manually, i can import into a clone file by splitting the repeating field, and then making a new layout in the clone file. the resulting reported dates do match the names. but, i would like to automate (script) the (1. opening the clone file, 2. the importing of the records, 3. splitting of the records, and 4. opening the report in preview mode). but, in scriptmaster, i donot know how to script a splitting of a repeating field...

thanks for helping

[CobaltSky]

BruceR wrote:

You can't report on repeats.

That's odd. I believe I just cited two very straightforward methods for reporting on the repeats in this instance...

While it's true that repeating fields should be used with caution, and NewFMuser has queried whether they should be used in this insrtance, it's also true that we haven't been given enough information about why they have been chosen and what, more broadly, is to be accomplished by the set up. Moreover it is not very useful to say 'don't use repeating fields' to someone who is new to FM without suggesting some alternatives.

Which brings me to the point. The alternative to repeating fields, as a rule, is a related table and a portal to that table. So what is a portal?

A portal is in interface object which can be 'drawn' onto a layout and linked to a relationship such that it can display a list of related records sourced via that relationship. It's a powerful thing and is one of the keys to managing interaction between tables which have one-to-many relationships. But it is a big topic.

Before moving forward with your project, I suggest that you take a little time out to read the manual and online help entries on portals (there's a bit much detail there to reproduce in a forum post), and to experiment with creating a portal or two in a test file, so that you can get the feel of them and understand a bit about their capabilities. You'll then be in a better position to consider whether they may provide a desirable alternative for the requirements of your current situation.

Once you've done that, you may also have some more specific questions about portals. You will find that there is an entire forum area devoted to portals, which is at:

http://www.fmforums.com/threads/postlist.php?Cat=0&Board=UBB11

- and of course, if there is anything you are still unsure about after browsing the documentation and the portals forum, just post another question and I'm sure folks will be glad to offer further pointers.

[bruceR]

"That's odd. I believe I just cited two very straightforward methods for reporting on the repeats in this instance."

This depends on how you read the original problem statement. And it is certainly true that your lengthy reply is more helpful. But it is unclear, for instance, whether there is only a single related Visit record for each patient, holding a repeating field with multiple dates. Or multiple related records. Or quite possible NO related records, he may mean that the patient table display a visit date repeat field. In all cases there would be no go to related record operation, just a subsummary report in Visits sorted by patient.

NEWFMUser, you do not want to script the process. You want to get rid of the repeats completely. You can split them off once as your first step in the process.

Here are some of the many problems with repeats:

Want to sort them? Can't do that?

Want to insert a new date in the middle? (Maybe you missed an entry for a particular data). Can't do that.

Need to delete an entry and have the others roll up? Can't do that.

Want to do a report by date? For instance a report by day of week showing everything that happened Monday across all patients, then Tuesday, etc. Can't do that.

Want a calendar view? Can't do that.

Maybe you have a date AND a description - routine checkup, toe fungus, whatever. Want to report by description? Can't do that.

Want to view them in a portal attached to the patient record but dynamically select the type or sort order? Can't do that.

I think you probably want related records instead, with a date and a description field (what was the purpose of this visit?).