numbering multiple records with one id

[snaves]

I hope you can help. I have three dbfs-one is the parent (one to many), objective and intervention.

The trouble I am having is the objective dbf counter. The user creates a parent file-then jumps to the objective database to create multiple objectives for one parent record for example

Parent dbf unique id

Objective 1 (this is counter field)

Intervention "1" (in intervention dbf)

Intervention "2" (in intervention dbf)

Objective 2 (this is counter field)

Intervention "1" etc.....

This is what I have that is not working

Perform Find (based of Primary ID)

Set Field [FFSObjective::counter=0]

Go to Record/request/Page [First]

Loop

Set Field [FFSObjective::counter; FFSObjective::counter +1]

Go to Record/request/Page [Last]

End Loop

When I run it, if I have 3 objectives—it only displays a [color:red]3 not Obj [color:red]1, Obj [color:red]2, Obj [color:red]3

Thanks in advance!

[NovaChan]

Hi snaves,

I'm not sure I understand your post well enough to comment effectively. Please comment on my summary below:

You have one FileMaker file with three tables in it - Parent, Objective and Intervention. For each Parent record, you would like to assign several Objective records. Each Objective can also have multiple Interventions. You are trying to number the related records 1 through 3, etc.

Is this correct?

In that case, I would suggest setting up an auto-enter field for this purpose. It would look like this (in Objective::counter):

Max( OtherObjectivesForParent::counter ) + 1

This will add the next higher integer to the counter field every time an Objective is created. It would also allow you to change the number order if the "replace existing values" checkbox is left unchecked.

Edited September 16, 2009 by Guest
Explained reason for auto-enter field

[mfrapp]

using Get(RecordNumber) in a unstored calculation field would also display 1,2,3 if 3 records are related

[NovaChan]

It's always good to hear another option. One note about using Get( RecordNumber ) is that the results will be dependent on context (like a portal's sort order). That makes it easy to use for different interface purposes, but it can't be treated as data in most cases. In other words, it probably won't be helpful if you want the numbers to remain consistent, such as for a report.

[comment]

One note about using Get( RecordNumber ) is that the results will be dependent on context

I believe that's a good thing. In general, storing data that is dependent on sibling records is not good practice. If a record is deleted or re-assigned, the data needs to be adjusted. Not to mention that auto-entering Max() + 1 can generate duplicates if two users are creating a new record at roughly the same time.

[NovaChan]

Definitely valid concerns, comment. Out of curiosity, how would you approach this field if it did need to be treated as data?

[comment]

I don't perceive of such need. These are conflicting requirements: if the data is stored, it shouldn't depend on other records and vice-versa.

[NovaChan]

I'm afraid I'll need some elaboration on this before I get your point.

I'm thinking of a case where we would want to assign a particular order to the records in a portal. This could be in creation order by default or modified into an alternate sequence. But the sequence would need to be stored.

Are you saying that this scenario shouldn't arise or should be handled differently?

I suppose I'm talking about a crude/implied recursive relationship. You could go all out with a foreign key for each record's ancestor and descendant, but I'm not sure that's worth the trouble.

Edit: On second thought, I don't think it's necessary to have a key for both the descendant and ancestor, just one.

Edited September 17, 2009 by Guest

[comment]

OK, let's go with that example. And let's say that in order to ensure the defult sort order, child record are initially assigned their serial ID as their rank number.

Note (and this is important in the current context) that the serial ID's - and consequently the rank numbers - are NOT necessarily consecutive for the children of the same parent, not do they start anew for each such group.

Now, changing the sort order could be a relatively simple matter of swapping the rank numbers of two records (this is assuming that the change is intended to apply to all users, and ignoring possible issues due to records being locked by other users).

[NovaChan]

I can see how one might use two fields for this:

SortOrder could contain UID or RecordID values for ordering the records.

SortDisplay could be calculated from Get( RecordNumber )

Having a separate display field would relieve you from cleaning up SortOrder in the event of a deleted record or change of order. This approach also resolves your issue with records receiving duplicate values should they be created at the same time.

But I'm still not sure how I feel about the display value being dependent on other factors like my portal's sort order. I'm also not sure how it would work in a list view.

Edit: I suppose you could use an unstored calc in a third field to calculate the display value for a list view if necessary.

Edited September 17, 2009 by Guest

[comment]

The "clean" value could be calculated through a self join, if necessary. Though I don't see why you would want to display it in a portal/list that isn't sorted by it in the first place. If it is, all you need is to type @@ on the layout.

This approach also resolves your issue with records receiving duplicate values should they be created at the same time.

I don't think so. Duplicate rank numbers are still unacceptable, if you want users to give users tools to shift portal rows up/down. If #4 is now behind #4, how will you ever get it to move up one row?

[NovaChan]

I don't think so. Duplicate rank numbers are still unacceptable

What I was trying to say is that the initial order values would not be duplicates since the UID or internal record number would by definition be unique.

The routine for changing the sort values is another matter. I was assuming for the sake of your argument that you had an alternative method for manipulating the record order. Moving a record by more than one placement or row would require more than swapping ID's. If I was moving record 4 between 1 and 2, for example, I would set the new order value to a decimal value between the values for 1 and 2, sort the related records and then replace the field contents with a serial value (assuming handling of record locking, of course). In that case, I would be left with a range of integers from 1 to 4.

I'd actually be more inclined to just display those values instead of the calculated Get( RecordNumber ) because I don't need the flexibility of separating the data and display aspects. That's one reason why I chose Max()+1 for setting the initial value.

[comment]

I am afraid I am not following the logic here - it seems to me you have now linked back to the beginning of the discussion.

In any case, I don't want to hijack the thread and change the subject to methods of ranking records, esp. since it is a rather exceptional case:

As a rule, an attribute describes the record - not its relationship to other records. Allowing users to rate the records comparatively breaks this rule. Strictly speaking, such rank is not an attribute, but a relationship. But I doubt anyone wants to go there for the implementation, so we have an exception - along with all the problems associated with breaking normalization rules.

[NovaChan]

I agree that our discussion has endured long enough. I find it a relevant case since using numbers in the first place inherently implies a relationship. I've encountered it before.

I was interested to explore the idea because, like I said at the beginning, I'm not convinced that normalization is always necessary in this case. It all depends on the requirements, but it's helpful to think over some of the implications of a design issue before being confronted with it, especially for our members with less theoretical background or experience.

Thank you for entertaining the case.

snaves, did you get what you need?

[snaves]

I couldn't get it to work-my first ponderence was--do I copy it into auto-entry--calculations or Auto serial calculations? These are dbfs with relationships. Hopefully this will help.

Thank you for advising--

DBF_screen.PDF

[NovaChan]

Before going further, it might help to clarify any other relevant details regarding your issue. Is there any significance to the order numbers?

If all you want to do is number the portal rows 1 thru n, mfrapp's method is the simplest way. That is a calculated field. Make sure it's unstored.

My method was in a number field that is set to have a calculated auto-enter value (that does NOT replace existing values).

If you're planning on moving the portal rows up/down to change the order, Excelysis has a relevant demo on their downloads page. It's called Portal Line Shift.

[snaves]

The numbers would be fixed--I try a number things (mainly trial and error) both in coding and scripting. I would upload the dbfs for you but don't know how to do that yet..I thought passing a value to a variable might work but couldn't figure out how to do that...

[NovaChan]

I believe database files need to be compressed as a zip file to be uploaded. I'm sure you can find a tutorial on that if you're not familiar with creating archives/zip files. Be sure that you completely close the file before doing anything to it though. If your database has more than one file, it's a good idea to close FileMaker completely before copying or moving one of the files.

It will be easiest to have a copy of your file for other forum members to look at if you have more questions.

[Lee Smith]

I recently described the process for attaching a file Here

[snaves]

Hopefully the dbfs zip and attach for you. I know it seems like alot of bouncing between dbfs--and wasted space in the portal which does not resize. You will probably need to dummy down suggestions as you can see my Spaghetti logic and trial & error methods. Thank you for any and all guidance!

FamilySupport.zip

[NovaChan]

snaves,

Thanks for uploading the database.

I made a few revisions to the interventions file to show you how the number fields can be utilized.

I see that SecondaryID is formed using the FamilyID and the ObjectNum. Is there a particular reason that you're incorporating the FamilyID? I don't have much experience with compound or composite keys because I try to avoid them. Someone else may be able to offer more helpful comments regarding how this key is being used. One problem I see is that the ObjectNumber value is not required nor necessarily unique

I created another table occurrence group in the Interventions file to show how a more typical set of relationships would look. With this method, you know that each ID is unique and that relationships don't interfere with each other. For example re-assigning an Object to a new Family won't require any changes to the Interventions. It's also important that, if ObjectNumber is an attribute (i.e., describes something about the record), it is not part of the key.

If you haven't tried this yet, I recommend finding some good examples to dig through. You have to be careful with demo files, unless they address the specific technique you're interested in. They're often built in haste or with very limited purposes in mind. Some of the FileMaker templates are very clean and have additional notes in them.

Reading and revisiting an introduction or two on database normalization is a good idea too. It can be tiresome but could save you from some headaches later on.

FamilySupportRevised.zip

[snaves]

Thank you for all your help--it will take me a while to think through this...I will definitely follow up your suggestion forreading up on normalization-I will keep you updated on the progress

[snaves]

The dynamic works great and does exactly what I wanted. The static didn't change at all and stayed a "1" when creating new ones. Can you recommend a book to read (I didn't like Filemaker for Dummies). Thank you for all your time and guidance.

[NovaChan]

I'm glad the numbers are working for you. I won't even bother diagnosing what's going wrong with the other implementation.

As far as books go... I'd normally try to redirect you to another thread, but I didn't see one that looked relevant. There are several that come to mind for consideration:

- Special Edition Using FileMaker 9 by Jesse Feiler

I have this book for version 8 of FileMaker. I've found it (and other books by Que Publishing) to be very practical. I wasn't particularly impressed by the book initially, but I ended up returning to it several times for specific examples as I was becoming acquainted with common practices in FileMaker.

- FileMaker Pro 10 Bible by Ray Cologon

Dr. Cologon is a well-respected mind in the FileMaker community. I've got the version 9 book, and I found it to come from a more theoretical angle. That's not to say it's dry and complicated, because I don't think that it is. I'd grab for this one if I wanted to change the way I think about FileMaker. If you want to begin developing with a broader perspective in mind, then maybe this one would be good for you.

- FileMaker 10: The Missing Manual by Prosser and Coffey

I haven't looked at this book, but Geoff Coffey is both a skilled developer and presenter. His presentations and blog posts have always been informative for me, so I'd give this book a look. Maybe someone else can comment on its usefulness for them?

- FileMaker Professional Training Series

This is the standard text for people beginning to prepare for the certification test. It walks you through all the fundamental aspects of FileMaker and provides examples. It's also considerably more expensive than the other books (abo $100). It might be worth it to you if you can get one used though.

There's also online options like Chris Ippolite's lessons on Lynda.com and Matt Petrowski's series of videos (some of which are free) at ISO FileMaker Magazine.

There's a few other books out there. I see Jesse Feiler has come out with one for FMP 10. I don't have any experience with them though.

[Lee Smith]

Hi NovaChan,

That is a good list.

Hi snaves,

You can find Reviews of books and FileMaker products in the Product Review Topic Area Here

HTH

Lee

[NovaChan]

Thanks Lee. And thanks for pointing us in the right direction. It's nice to see others' input and to try to keep things DRY and organized.