Serial increment with (or witout) value list

[zab]

Hello brilliant minds

I need to do a SerialIncrement but I can't find the right way.

I have a line item table with those values:

dining room

master bedroom

bedroom

kitchen

...and more

then in another table, with a pop up value list, I choose rooms from the line item

master bedroom

kitchen

bedroom

bedroom

bedroom

Other fields describe items included in all of those rooms

My question is

How can I have those bedrooms to increment each time I add a new one?

bedroom

bedroom 2

bedroom 3

Any idea?

Thank you.

[bcooney]

Need more info. You mention a line item table, but what is the parent table? Is this an invoicing system?

[comment]

I would define a self-join relationship of the child table (LineItems) as:

Child::ParentID = Child 2::ParentID

AND

Child::Category = Child 2::Category

AND

Child::ChildID ≥ Child 2::ChildID

then count the related records in Child 2 in order to get the ordinal suffix.

Note that changes, e.g. deleting a record or re-assigning it to another category, will cause automatic renumbering.

Edited July 10, 2009 by Guest
Generalized the technique

[zab]

Hello

OK that sound good but I think I made a mistake in my description so what I called the line item is in fact a simple table with a list of rooms (ids and names). The line item is really the table where I compile the rooms and where I want them to have a number in the end. Is your explaination still good? It certainly look like exactly what I need. I'm not sure what you mean by count the record in the line item 2 ? How? I will try this tomorrow.

Thank you

[comment]

what I called the line item is in fact a simple table with a list of rooms (ids and names).

That's what I understood - in any case I have edited the previous post and made it more generic.

I'm not sure what you mean by count the record in the line item 2 ? How?

By a calculation field in the Child table (evaluated from the context of Child) =

Count ( Child 2::ChildID )

[zab]

Well it dod not accomplished what I was hoping for. I need to create a selection of the room (a menu) the way it works is when I select a room in the menu, if I have 3 bedrooms It will show me "bedroom" only one time. To seperate them I simply create a calculation that merge the bedroom and its dimension. It differenciate them. But for my menu it seem the only way it can show me all the rooms is if it's I sort them with the serial number. But it will look bad.

So my new idea is to assign a number to every different room related to a house. Like a serial number but to be nice it should start over to 01 with every new HouseID. I was trying with Count but it's not working . Simply putting if there is 15 rooms, all will have their own numbers and it will look good on the menu. There must be a simple way that my still novice mind is missing.

Thank you

[comment]

You didn't mention you wanted to use this in a value list. Since it's an unstored calculation, it won't work well - unless you select a unique ID, and use the "Bedroom 2" as the second field in the value list. It would probably be better to use another method of selection, for example a portal.

I don't know of a RELIABLE method that would assign permanent (stored) numbers with restarting for each parent's children.

[zab]

Hello again

I accomplished what I need but it's ugly. In that part of the project I mostly use my model tbl to store everthing general. Then I added Tabs to bring the specifics. The goal is to be able to do everything in the model tbl. So I have portals that bring the datas from the line items. I have a tab with the rooms portal in it and then on the next tab, wich compile the electricity items from every rooms I have created a menu that bring every rooms and then I can add items specifics to that room in the portal. You're following?

Model with tabs-> Rooms (portal) ->Electricity (portal)

and a menu to sort the portal by rooms.

So in the electricity tab I have my Item portal and for the selection menu I use a value list wich bring the rooms in relation with the model ID. If I use the "room Id" and then the "room name" in the value list it only shows me one ID, in other word, if I have 3 bedrooms it will sow me only one. Si I used the "Serial #" with the "room name" and then it shows me every bedroom related to a model.

The thing is to differentiate all the bedrooms I made a calculation that bring the room name with the dimensions (bedroom 12x14). That's a great way to differentiate them. But because it's a calculation field that can't be indexed. It can not only show the second field in the menu. So it's very ugly.

I'm trying to find a way to remove the ID or serial from the menu.

That's why I was trying to add a field that would count the number of rows (or rooms) from let's say 1 to 20. that would not be too ugly to see in the menu.

It's really an esthetical problem at that point. I just can't imagine seeing a menu that look like "876 Bedroom 12x14" all this because I have no other unique way to sort them but by using the serial#.

I would not mind having the Room_id if it could show me all the rooms.

There must be another way

If you have any idea. ;-)

Thanks

[comment]

I made a calculation that bring the room name with the dimensions (bedroom 12x14). That's a great way to differentiate them. But because it's a calculation field that can't be indexed.

Why not? What's this calculation using as its input?

I would not mind having the Room_id if it could show me all the rooms.

Have you tried my original suggestion? It's possible it would work even if you selected not to show the ID - though that is something I myself wouldn't dare to rely upon.

[zab]

Why not?

Because this calculation field use another calculation field from a related field (the room name come from the table list of rooms).

Edited July 14, 2009 by Guest