Clearing the right repetition

[GC GYM]

I need a script that will result in removing the contents of a portal row by clearing the field that created the relationship. I definitely do not want to delete the related record from it's table.

I can navigate to the related record and copy the ID but have nowhere to go from there, or I can navigate to the ID field in the same table but I can't figure out how to get it to choose the repetition that corresponds to the record whose data was displayed in that particular row of the portal (self join).

I see that the option to go to repetition number by a calculation is there, I just can't figure out what to put in the calculation, or if there's a better way.

I imagine that I could have a separate script and button applying to each repetition in the field, but that doesn't seem very efficient and I still can't make it happen by clicking a 'remove' button on a portal row.

Any help greatly appreciated - hope this wasn't too vague.

[T-Square]

Welcome to the Forums!

From what you first describe--i.e., that you want to clear the field that creates the relationship--I was going to say that you should be able to script going to the correct record in the remote table and clearing that field.

But then you mention the word "repetition", and all the red flags fly up. I suspect that you are trying to use a repeating field to store multiple links from one table to another. If this is in fact what you're doing, you should seriously consider changing your database structure to use a "join table" to facilitate a many-to-many relationship.

A join table at its most basic will contain the unique ID fields for each end of the connection (e.g., a LineItems table would have an InvoiceID [to link to the Invoices table] and a ProductID [to link to the Products table]). Creating relationships between the 3 tables makes it possible for you to create a portal in your Invoice layout that is based on the LineItems table, but shows information from the Products table. Each row in this portal represents a row in the LineItems table, so deleting a row from the portal doesn't delete a product--just a record that links this invoice to that product (thus solving your original problem).

If I'm off base, let me know, and perhaps we can find a solution.

David

[GC GYM]

Thanks David and UH OH!!

Quadruple the red flags, because I'm using no fewer than 5 repeating fields to make 5 different relationships.

To put it in context, we are in a table called classes, and I'm enrolling students (from a table called Students) into the class by entering their id into one of the repeating fields (In class, wait list, drop list, or makeup list).

Just for good measure I'm adding teachers from another table using a repeating field Teacher ID.

As I digest it, what you're saying makes sense, although with the exception of this little hiccup, the solution has been working great. The sinking feeling I have is that I pretty much need to start again from scratch with a table called enrolments, holding a class ID and a student ID in each record?? Or do I need 4 tables, one each for In class, waitlist, droplist & makeup?

Thanks,

Phil

[Søren Dyhr]

If I understand you correctly could you make a multiline text field behave exactly the same, since it seems here like we're dealing with a join table free many2many relation.

I use this quite often, when no kind of statistics is required from the join table. The way I manage this with one single script is a scriptparamter wich is used as ListB in this custom function:

http://www.briandunning.com/cf/39

This makes the script very close to a singleline script containing a single "Set Field[" making a "isit or isit ain't" but if you don't have it attached as a button in another portal will you need to lift the paramter in via a scripted recursion.

--sd

[T-Square]

Søren--

I'm surprised to hear you (the regent of normalization!) suggesting that Phil use repeating fields... :

I have a hard time imagining that a class enrollment application would NOT need statistics (number of enrollees, tuition totals, etc., etc.). Therefore, my suggestion stands--especially since it handles Phil's problem efficiently.

A single join table for enrollments would work. You could add other aspects specific to the enrollment in that join table, such as your enrollment status, tuition paid, final grade, etc. With a little creativity, you could set up your system to limit the number of enrollments (using a calculated field in Classes to count Enrollees and prevent addition of enrollments), preventing overbooking. This could also be used for determining a teacher's pay, if such depends on the number of students enrolled.

A second join table would be advised for the class-teacher connection, since it is possible to have more than one teacher for a class.

Once you build a new prototype, it is possible to populate the join tables after the fact. Others might have ideas how you can do this within FM; I typically have dumped the data out to a file and worked the data in Excel until it's the way I want it, and then import it back in.

HTH,

David

[GC GYM]

Many thanks guys. That was very helpful. Yes I have mountains of Stats coming off the class enrolment data.

Now, back to the drawing board!

Phil

[Søren Dyhr]

I'm surprised to hear you (the regent of normalization!) suggesting that Phil use repeating fields...

I have a hard time imagining that a class enrollment application would NOT need statistics (number of enrollees, tuition totals, etc., etc.). Therefore, my suggestion stands--especially since it handles Phil's problem efficiently.

I have a hard time as well imagining this lack of statistical use - It must have been some grammatical inadequacies in my post, where I said if the statistics really could be avoided, could it speak for the elimination of the join table - and I also suggested to substitute the repeater with a multiline text field, since it eases the handling.

I have nothing against using the repeaters as key fields, and thats how I understood the question, and do often use them when a genuine recursive function is too much trouble to develope.

--sd

[T-Square]

Søren--

I _sincerely_ hope you took my comment as a jest, as that was my intention.

Thanks,

David

[Søren Dyhr]

I did don't you worry, but the grook goes like this:

THE ETERNAL TWINS



Taking fun

  as simply fun

and earnestness

  in earnest

shows how thoroughly

  thou none

of the two

  discernest.

Source: http://www.chat.carleton.ca/~tcstewar/grooks/grooks.html

...in short - I had it comming!

--sd