create string to represent order (1; 1.1; 1.2.1, etc)

[H1sc]

Hello, I have a problem of speed on a database that I'd really like some help with.

 

I have a table of topics. Each topic is related to another topic in a parent-son relationship.

 

Example:

 

       Topic:                            Father:

  filemaker pro                1 (my code for father-of-all)

  complex calculation     filemaker pro

  custom functions         filemaker pro

  balsamiq mockups      1

  features                        balsamiq mockups

  price                             balsamiq mockups

 

what I need to do is to make them ordered in a way such as:

 

1. Filemaker pro

1.1. Complex Calculations

1.2. Custom Functions

2. Balsamiq mockups

2.1. price

2.2. features

 

I'm able to do the order_number, but in a so slow way. I have for now 800 topics, and it takes like 16 seconds. As I have to do it a lot of times a day (every time someone adds a topic, or wants it to be in a different order, I have to reorder), I'd like to have a faster implementation

 

Can someone help me on what the ideal implementation should be? I'd love if it was fmp, but it'd be fine in pseudo-scripting.

 

thanks for all the help.

[comment]

Is this for a report, or do you want this to be calculated at all times (thus ignoring any found sets)?

[H1sc]

Ideally, if both ways were easy to describe, I'd like to know both. But, for now, I want to ignore the found sets.

[comment]

None of them is very easy to describe. There is also an additional complication in:

 

As I have to do it a lot of times a day (every time someone adds a topic, or wants it to be in a different order, I have to reorder),

 

I am not sure how that's done. For now, I am going to assume that each record has a Rank field, indicating its order among its siblings. How this field gets populated is a separate issue.

 

Note also that records need to be related by ID, not by name (which you may want to change, without breaking the relationship). So our starting point is something like:

TopicID     Topic                ParentID        Rank
1           filemaker pro        0 or empty      1
2           complex calculation  1               1
3           custom functions     1               2
4           balsamiq mockups     0 or empty      2
5           features             4               2
6           price                4               1

Now we need to define a calculation field cParaNum (result is Text) =

Case ( ParentID ; Parent::cParaNum & "." & Rank ; Rank  )

Note that this a cascading calculation, so it will never be very fast. I don't know how slow it will get with the amount of records you have. OTOH, since it is unstored, it will not evaluate unless needed - so before you place it on a layout, make sure you do need it there. If that's too slow, you will have to resort to a scripted population of a stored field with the contents of the unstored one.

[H1sc]

You are awesome!!! I had like a 40 steps script for doing that, and you did it with one calc and one t.o.

 

By the way, I had already related it by ID before. Made the mistake before, but when I came to ask it was alright.

 

If it's not too much to ask, I'd like to know if an upgrade would be possible (seriously, I already think it's awesome). Would it be possible to not repeat a number.

 

Ex: I have two records ranked as 1 with the same parent, they would have the same cParaNum. Could it be that the calculation understood that one would be treated as 1,01 and the other as 1,02?

 

thanks for all the help so far!!

[comment]

I have two records ranked as 1 with the same parent,

 

Well, I was expecting a follow-up when I said:

 

For now, I am going to assume that each record has a Rank field, indicating its order among its siblings. How this field gets populated is a separate issue.

 

However, I was expecting a problem with gaps, not a tie. Is there a chance you could prevent these duplicates to occur from the start? Because they are going to be very difficult to handle (I'm not even sure if it's possible without scripting). Just consider: it's not only that one them needs to be shifted up to 2; it's also all the other siblings need to move back one place (two places if there are triplicates, and so on).

 

I would suggest you find another way to reorder the records - one that would not allow duplicate ranks. For example, you could let each new record auto-enter its TopicID as its initial Rank; then promote the topic by exchanging its Rank with the topic immediately above it. There is a demo of this method somewhere; I'll try to find it for you.

 

---

There it is:

http://fmforums.com/forum/topic/60325-manually-reorder-portal-records/#entry285451

Edited January 15, 2014 by comment

[H1sc]

I'm sorry, I think I didn't notice the need of a follow-up. I can do in a way as to avoid tie/gaps (maybe with script).

I'll take a look at the attachment soon (headache now). Thanks for all the help so far.

 

[comment]

Gaps are relatively easy to handle; ties are not.