Sorting repeating fields

[Robert Collins]

I am wondering if there is a way to alphabetically sort repeating fields.

I have a table called 'products' and a field called "specification" which has 12 repetitions listing various spec on a product. Via a checkbox field the user can tick the features that the particular product has.

To keep the records tidy (and for reasons below) , I would like the ticked options sorted as I can't trust the user to click on the options in the right order every time.

Also I would like to use a global field with the same repetitions in conjunction with conditional formatting as I have a layout with all the products listed. At the footer I would like a global field that the user can tick product spec criteria and the records without that exact spec change colour.

I have the global field set up but if I use a simple match formula, unless the repeating fields were entered in the right order, it doesn't work.

I thought if the repeating fields with sorted alphabetically it would solve this problem.

Any help would be appreciated.

[bruceR]

I am wondering if there is a way to alphabetically sort repeating fields.

I have a table called 'products' and a field called "specification" which has 12 repetitions listing various spec on a product. Via a checkbox field the user can tick the features that the particular product has.

To keep the records tidy (and for reasons below) , I would like the ticked options sorted as I can't trust the user to click on the options in the right order every time.

Also I would like to use a global field with the same repetitions in conjunction with conditional formatting as I have a layout with all the products listed. At the footer I would like a global field that the user can tick product spec criteria and the records without that exact spec change colour.

I have the global field set up but if I use a simple match formula, unless the repeating fields were entered in the right order, it doesn't work.

I thought if the repeating fields with sorted alphabetically it would solve this problem.

Any help would be appreciated.

This (data manipulation) is one of the many problems associated with repeating fields and a reason they are generally not a good idea for storing primary data. However in this case it isn't clear why you need a repeat. If it's a checkbox field, why not just have one rep?

[Robert Collins]

thanks for the reply

The reason I thought to use a repeating field is that it would be easier to manage 1 field (and easily add options to the list) than 15+ fields.

However, it looks like that's exactly what I need to do.

[comment]

I think you are missing the point. I cannot tell for sure, because I don't understand where a repeating field comes into this either. Can you post a simple file showing what you have now?

[Robert Collins]

Sorry, my mistake - repeating fields don't come into it. I was confusing this with multiple values in one field (using a checkbox value list)

I shall start again.

In my product table would like a value list with checkbox input so a user can tick the features a product has (maybe 10-15 but could be more)

This information is then to be used in

1)a letter that I send my customers

2) in s product selector page in conjunction with a globalfield - the idea is that the user can tick the product features and only the relevant models will be displayed in a different colour (with conditional formatting)without having to to a find.

in both instances, I cannot account for the user ticking the options in the correct order when creating a new product record so the feature list of each product could be in a different order which makes a simple calc (phone spec=phone spec_global) difficult.

I suppose I really need the flexibility of seperate fields for each product feature , but thought there maybe another way to do it.

I hope this makes things clear(er)!

[comment]

The first part is easy: make your field auto-enter a calculated value (replacing existing data) =

FilterValues ( ValueListItems ( Get ( FileName) ; "MyValueList" ) ; Self )

This will keep the selected values sorted in the order of your value list.

The second part is not too hard either (if I understand it correctly). But it cannot be done by looking for = globalfield. Suppose I have checked Red and Green in the global field. Shouldn't a product with Red, Green and Blue be highlighted as well?

Try highlighting the field using a formula =

ValueCount ( FilterValues ( YourTable::gValues ; Self ) ) = ValueCount ( YourTable::gValues )

and

ValueCount ( YourTable::gValues )

where gValues is the global field. Note that this should work regardless of the order in which the values were selected, in either field.

[Robert Collins]

thanks very much , I shall try that out now.

[Robert Collins]

The first part is easy: make your field auto-enter a calculated value (replacing existing data) =

FilterValues ( ValueListItems ( Get ( FileName) ; "MyValueList" ) ; Self )

This will keep the selected values sorted in the order of your value list.

The second part is not too hard either (if I understand it correctly). But it cannot be done by looking for = globalfield. Suppose I have checked Red and Green in the global field. Shouldn't a product with Red, Green and Blue be highlighted as well?

Try highlighting the field using a formula =

ValueCount ( FilterValues ( YourTable::gValues ; Self ) ) = ValueCount ( YourTable::gValues )

and

ValueCount ( YourTable::gValues )

where gValues is the global field. Note that this should work regardless of the order in which the values were selected, in either field.

Ok, I did the first bit but I have a question regarding the conditional formatting formula

All fields seem to point to the gvalue table rather than one of them relating to the field in the product file table. Do I have this right?

thanks

[comment]

I don't understand what you are saying here:

All fields seem to point to the gvalue table rather than one of them relating to the field in the product file table.

See if the attached helps.

MulitMatchHilite.fp7.zip

[Robert Collins]

Thanks for the example. That is what want to achieve but the difference is that the layout I am using is diplaying fields from another table , but is using portals to diplay info from the product table.

I shall have a fiddle about as I am nearly there.

My intial mistake was that the g_values field was in a different table and not in Products

thanks again

[comment]

I don't think it matters (for a calculation) in which table a global field is.

[Robert Collins]

I have edited your example to show what I am trying to achieve.

In the sales layout I would like the different products in the 2 portal rows to change colour when they meet the criteria of the global_field selector at the bottom of the layout.

this is a tiny part of my database so things may look strange out of context

See what you think

thanks

[comment]

Same thing, I think.

MulitMatchHilite_edit.fp7.zip

[Robert Collins]

:laugh2:

Thank you very much, that is exactly what I needed :smile2: