Sorting portal

[Adri Oosterwijk]

Hi there,

I have a portal on my layout showing related records. Lets call the (portal)table Items. (I have to translate some because the names I used are in Dutch.) Items is a child file of Branches wich is a child file of (lets say) Headquarter. Each branch can make their own Itemlist. The items are identified by the user by using a identifier, for example "RES10". It is very likely that the other branches uses the same identifier as well, with different parameters such as description and price.

The identifier may be several times in the Items table but only one time for each branch. So a validation by Unique value in Define Database is not working (it checkes the records owned by other branches as well).

Ok, so far so good. I've created a script wich checks for duplicates. The script is triggerd when leaving the identifier field. Works great.

Now the question. How can i get my portal sorted on the identifier field? The standard behavior is not working because when the Check script is triggerd the focus is removed from the identifier field and the sort performed. That leaves me with a sorted portal (the record involved in another place) and the focus on another record and field.

Please advice.

hth

Adri

[comment]

I don't understand your description. A portal is sorted by the field/s specified in the portal setup. What does this have to do with checking a field for duplicates?

[Adri Oosterwijk]

Thanks for your reply.

I will try to make it more clear.

This is whats happening.

I'm entering data in the field "Identifier". When I leave the field a script checks for duplicates. The script goes to another layout, perform a find and if a Get ( FoundCount ) function is > 1 it returns a dialog. By clicking OK the value of the identifier field is set to " ". What it looks like is that when the script is performing, the record where I'm entering data in is not the active record anymore. The portal settings perform the sort and when the script returns to the orignal lay-out and object the sort is done and another (the wrong) record is active.

And that is what I want to avoid.

1. I want to ceck for duplicates (and it has to be done by a script, in my opnion, because the validation setting in Define Database "Unique Value Only" watches all of the records in the table and not only the set based on the relationship)

2. I want to sort the portal by the Identifier field.

Perhaps I need to use a totally different approach. I really don't know.

I hope that you can help me out here.

Thanks in advance.

Adri

[comment]

OK, I see (hopefully). The validation does not have to be done by a script. In fact, it shouldn't be done by a script (at least not your script), because while the script is checking in another table, your record is already committed. There are several ways to do the validation - I believe the simplest would be to define another text field with auto-entered calculation (replacing existing value) =

BranchID & "|" & Identifier

Validate this field as Unique, Validate always.

I should also add that usually when users are required to come up with a unique identifier, something is wrong with the design.

[Adri Oosterwijk]

Thank you, You saw it right.

It is not the case that the users are required to come up with a unique identifier for the record itself. Ofcourse has the id field of the record an automatic entered value. The identifier field is more like a short articlecode wich the user easy can remember. Example: RES10 stands for Reservation for 10 years. I hope this will clear your last remark. Further on in the solution the user is able to select the short articlecode with a pull down menu or so to enter that lineitem in an order (lookup).

I implemented the suggestion you made and there are two issues. First, a little one. Validation take place after committing the record, not by leaving the field. Are there possibilities to change that? I couldn't found it. It woud be great if the user get prompted the moment there is a duplicate value found. Otherwise the user creates a complete new record and on the end it is complete deleted.

Second, on commit I'm presented with two dialogs: First that the field value is not unique with two buttons, Revert and OK. No matter wich one I choose there is a second one with the question if I want to undo all the changes made in the record. Again two buttons "Revert" or "Undo" (i think, because my FM is in Dutch) and Cancel. By clicking "Revert" the record is changed to its previous state but when I click Cancel I got yet another dialog withe the message Go to lay-out is cancelled, do you want to continue with this script? Yes/No and so on.

So it works but it's not the winner of the beautycontest.

Because all the data in my solution is separated from the interface and logic (external data source) I have to check in another table for duplicate values when I want to accomplish this with a script or so. Do you have any other suggestions?

I hope to hear soon.

Yhanks in advance.

Adri

[comment]

First, remove your existing script trigger.

Now, the simple solution would be to trigger a script OnValidate that would try to commit the record. The problem here is that if the attempt succeeded (i.e. the identifier was unique) your portal would resort itself. So a more complex solution is called for - like the attached, for example.

Note that the script triggered OnValidate is for "cosmetic" purposes only - the validation is enforced on its own.

ValidateUniqueChild11.zip

[Adri Oosterwijk]

Sorry to be such a pain but....

Tried it in your file and this is what came up.

When I enter a new value in a new record it works fine.

When I change a unique value to an existing one it works fine, there is a dialog.

But when I enter a existing value in a new record the script accepts it.

Is it possible that the script "sees" it as an empty field? Because of the cosmetic nature of the OnValidate trigger?

In the Data Viewer the field value is correct displayed but the record is not committed yet?

As far as I can see it in the Data Viewer the List ( Siblings::UniqueName ) function is not updated after every new entry.

I wil study it more, but when you have more ideas.... very much appreciated.

[comment]

when I enter a existing value in a new record the script accepts it.

Yes, that is a problem. It happens because the UniqueName field is the first field to be filled - and until at least one field has a value, the ChildID field is not issued a serial number. In this "limbo" there are no related records in the Siblings TO so any proposed value is accepted.

If you can't find a way to get at least one field filled before the Identifier, the solution will need to get even more complex.

[comment]

OK, I think this should work: change the test in the If[] script step to =

not Child::ChildID and not IsEmpty ( FilterValues ( Child::UniqueName ; List ( Child::UniqueName ) ) )

or

Child::ChildID and not IsEmpty ( FilterValues ( Child::UniqueName ; List ( Siblings::UniqueName ) ) )

[Adri Oosterwijk]

Great! I think you got it.

It works exactly the way I hoped for. At least in your solution.

I'm gonna implement it in mine and let you know if its working there also.

Thank you very much!

[Adri Oosterwijk]

I implemented it in my solution and it works great.

It even maintains the, as you called it, the uniqueName field the active one.

Couldn't be better.

I was trying to accomplish this with a very complex script with perform find and so on.

This solution however leaves me wit one extra TO and 1 if statment in a script.

I'm learning every day.... and I have to.

Thanks again.

[comment]

This solution however leaves me wit one extra TO

Let's get rid of that, too.

ValidateUniqueChild11.zip

BTW, I believe there is a much simpler approach: have the script try and commit the record. However, this wouldn't work in your situation, because if the attempt to commit succeeded (i.e. the proposed value was unique), the portal would resort and you would lose your place.

[Adri Oosterwijk]

Indeed, this doesn't work because of the Portal sort.

I did not try your last solution yet (not the extra TO) because I was sorting out some other possibilities.

I'm sure that those possibilities will complicate things but I think it will make life a lot easier for the users when we can accomplish this:

If Child::UniqueName is new (doesn’t exist in Child table):

Accept en go to field Child::Value

If code is new for this Parent but not in Table Child

Exist already once in table Child

Set field Child::Value to value of found record (Child::Value)

Set other Child::Field to value of found record (Child::Field)

Etc.

Exists more than once in table Child (because they exist for more then one Parent record

Compare if the records are identical (in fields needed -> Child::Value and Child::Field etc.). The id field differs anyway so we can’t compare the complete records.

If yes: Set field Child::Value to value of found record (Child::Value)

Set other Child::Field to value of found record (Child::Field)

Etc.

If no: Show list with records (dialog or popup window in list view)

Add values of Child::Value and Child::Field etc. In the just added record by double clicking on one of the list items.

By clicking cancel, accept value of Child::UniqueName and go to field Child::Value, in this case the user have to add the field values by himself.

I tried to accomplish this with a script but found out that this was going to be a big script with a whole lot of Perform find commands in different TO's and it wasn't working yet very well. I'm kicking myself around that I'm not able to solve this task yet by myself. I'm learning every day but this is at this moment really beyond my skills.

I hope that you will find the time to help me out.

[comment]

I see several problems with your approach. But before that, I need to clarify something:

You must use field validation to enforce the uniqueness of the name. Field validation works at data level and does not depend on user being on a specific layout or on a script running successfully.

The only reason to trigger a script before field validation is user convenience: [a] we don't want users to deal with the confusing validation error dialog; and we want our script to alert users immediately, instead of waiting until they try to commit the record.

This is what I meant by "cosmetic". The true defense against duplicates is the field validation.

Now, in order to perform a find in the Child table, you must go to a layout of the Child table. How will you get there - when you have an uncommitted Parent record? Filemaker will not let you leave a record uncommitted - so the first thing user will see when running the script is the same confusing validation error dialog you were trying to avoid in the first place.

Consider also a scenario where user is editing an existing record, then changes her mind and re-enters the original value. This value will of course be found in the Child table - and user will be prevented from using it.

[Adri Oosterwijk]

Thank you.

Clear and simple. I've learned a lot. It iis clear to me that I can better choose for a diferent approach.

Simple said: Turn around and start over. Or don't you agree? Do I give up to easy?

I'm thinking now of a sort of central table for Items wich will be usable for every branch.

If the branch wants to have it another way, they can add it to their table/group trough relationship with the choice to add it to the central table. It's the other way arround but I think it's less complex to accomplish. Can you agree?

It feels like that I was trying to grow an appletree form a basket of apples while (in my opinion) the new appoach gives me the opportunity to pick apples from the tree.

Please advice.

regards,

Adri

[comment]

I'm afraid you have lost me at this point. What exactly is the problem you are trying to solve?

[Adri Oosterwijk]

Sorry for this late reply, but I've been away for a while.

What I want to accomplish is this:

Validation is OK so far.

I want to extend the script with the following:

When the field UniqueName exists one time in the child table (but with another Parent record) the Fieldvalues are copied into the record I'm adding. The user is able to edit the record.

When the UniqueName exists more then one time the field values in those records must be compared (UniqueName, Value etc. If they are the same in the records the values are copied in the record I'm adding. When they ar not the same the user must get the different records on screen by a layout showing a list. Unique records only. The user must ba able to doubleclick a record to copy the values into the record I'm adding. By clicking cancel the popupwindow closes, the Unique name is accepted and the cursor moves to the next field.

I was working on it and came to the conclusion that it was gonna be a large script with a lot of Find routines, switchint to different layouts and so on. I think it wil be slow. The question was: is there a way get this done in a quick way?

I hope to haer from you.

Adri

[comment]

Sorry, I don't follow you. Are you saying that in some circumstances you want to allow a duplicate value in the UniqueName field (for the same parent)?

[Adri Oosterwijk]

Thank you for your quick replay.

No, I don't want to allow duplicate values for the same parent. I want the possibility that if a value exist in the child table that it can be added to another parent. So the duplicate fieldvalues will be allowed in the child table. However those duplicates exists only at the level of some fields. Strictly speaking are the (visible) duplicates unique records with their own unique identifier.

If there are more "identical" records, all existing to different parents I only want to use or display one of them.

Sorry I wasn't clear (again) I hope it's clear this time. It must be my poor English. Thank you for your time to help me out.

[comment]

I want the possibility that if a value exist in the child table that it can be added to another parent.

What does it mean in practical terms? Suppose I am at parent record "Red", trying to add a child named "Alpha". This already exists in both "Red" and "Blue" sets - but not in the "Green" set. Do I now get the option to select "Green" as the parent for the new record? I must say it's hard to imagine a real-life situation where this would be useful.

[Adri Oosterwijk]

If you are at at parent record 'Red" and you want to add the child "Alpha" it is only possible when it is NOT present in the current set of parent "Red". When it does exist then the validationscript stops it all (this is the script you helped me with at the top of this topic). It is only possible to add this record "Alpha" when it does not exist in the parent 'Red" but does is Blue or Green. When it has the same values in each field (or it exist only once) the solution has to add it automaticly. When it has the same UniqueName but not exact the same fieldvalues the solution has to provide the user with an option to add the right one by doubleclick. So (forgive me if I didn't unerstand you the right way) as I read it is just the other way around.

the thought behind it is that the branches can use the records from eachother but also beiing able to add (or add and change) records with the same UniqueName to fit their own needs.

Case is this: I'm designing a solution to manage graveyards in The Netherlands. It is possible that there is an item what is called: "Fee for renting a grave for 20 years", costs € 4.500,-. That item will have a UniqueValue, lets say RENT20. It is however possible that one organisation manage more graveyards in a city (or given area). So it can be possible that the example fits the needs exactly for graveyard one, fits not for graveyard two because the price is different and fits not in any way for graveyard three because the description and the price differs but the UniquValue is the one the emloyees came up with because they will remember as the right one for their given situation. So, there are a lot of buts and ifs and elses and I'm trying to figure out a way to solve it.

Note: every single record in the Child table has its own unique identifier.

hth

adri

[comment]

Sorry, I keep losing you.

If you are at at parent record 'Red" and you want to add the child "Alpha" it is only possible when it is NOT present in the current set of parent "Red".

Correct.

When it does exist then the validationscript stops it

Correct. And if it doesn't, then the field validation will prevent it.

It is only possible to add this record "Alpha" when it does not exist in the parent 'Red" but does is Blue or Green.

Not exactly. You can add "Alpha" if it does not exist in the parent 'Red". It doesn't matter if it exists or not in the other sets.

When it has the same values in each field (or it exist only once) the solution has to add it automaticly.

No idea what you mean by that.

[Adri Oosterwijk]

Correct. And if it doesn't, then the field validation will prevent it.

The FM fieldvalidation is on a extra field wich concatenates the value of the ParentFieldValue and the UniqueNameValue Otherwise we are not able to allow duplicate UniqueNameValues in deifferent parent sets.

Not exactly. You can add "Alpha" if it does not exist in the parent 'Red". It doesn't matter if it exists or not in the other sets.

Yes, that is what I meant.

No idea what you mean by that.

I will try to describe it in a clear way.

Imagine this:

We are in browse mode working with parent Red and want to add the value Alpha.

We enter it in the field UniqueName.

The validation script returns OK and accepts it (it is does not exists in the "group" Red.)

The solution checks if the value Alpha exists in other parent "groups".

The value does not exist in another parent set

The solution takes us to the next field (Value)

If it does exists there are several possibilities

• It exists one time in another parent group
  The values are added in the child record of parent group "Red"
  Probably using some variables and SetField scriptsteps
  
• It exists more then one time in other parent groups (in more then one parent group)
  Example:
  
  Parent Blue
  UniqueName: Alpha
  Value: 100
  Price: € 10,00
  
  Parent Pink
  UniqueName: Alpha
  Value: 100
  Price: € 10,00
  
  Parent Yellow
  UniqueName: Alpha
  Value: 100
  Price: € 10,00
  
  Parent Green
  UniqueName: Alpha
  Value: 100
  Price: € 10,00
  
  The solution recognices the identical values in record one, two three and four and traet it if it exists only once -> the values are added in the child record of parent group "Red".
  
• It exists more then one time in other parent groups with differences (a mix) on field level.
  example:
  
  Parent Blue
  UniqueName: Alpha
  Value: 100
  Price: € 10,00
  
  Parent Pink
  UniqueName: Alpha
  Value: 100
  Price: € 10,00
  
  Parent Yellow
  UniqueName: Alpha
  Value: 200
  Price: € 10,00
  
  Parent Green
  UniqueName: Alpha
  Value: 200
  Price: € 50,00
  
  The solution has to come up with a dialog or popup window allowing the user to choose from:
  
  UniqueName: Alpha
  Value: 100
  Price: € 10,00
  
  UniqueName: Alpha
  Value: 200
  Price: € 10,00
  
  UniqueName: Alpha
  Value: 200
  Price: € 50,00
  
  It has to show every unique set of UniqueName, Value and Price only once allowing the user to select one of them.
  The fieldvalues are addes by doubleclicking the appropriate record.
  
• The one the user wants is not displayed
  The solution has to give the user the opportunity to cancel out. Doiing this the Popup or dialog is closed and the user is taken to the next field (Value) to add values manually.
  

I hope this will make it clear because I am running out of possibilities to describe it (perhaps due to my limited knowledge of the English language).

Thank you for your patience and willingness to invest the time to help me out.

hth

[comment]

OK, I think I now have a better idea of what you mean. But it seems to me you are trying to solve the wrong problem, so to speak.

Going by your approach, you would end up with 5 (or any number of) duplicate records in the child table - differing only by their ParentID. This is obviously redundant and requires a solution at the structural level.

Suppose you had 3 tables: Locations, Products and a join table of LocationProducts:

Locations -< LocationProducts >- Products

In the Products table, each product is unique (including the name, and perhaps a default price). For each location selling a product there is a record in the join table. This record holds the price (and other terms, if you like, including a preferred name) specific to the location/product combination.

This way you only need to make sure that the LocationID | ProductID combination is unique in the join table.

[Adri Oosterwijk]

Thank you for your reply.

Searching the forum to move forward on this problem the thought of a join table came up already.

Never used it so its a challenge (again).

I will try to work this out. I will let you know.

Thanks for all the time so far.

[Adri Oosterwijk]

Hi Comment,

I have implemented your suggestion and it works fine when I'm adding a record. The validation is handled by the code you provided. I only had to make sure the used List function evaluates every time a record is added (commited) or deleted.

I extended the script so that if the desired value exist in the general products table the corresponding fieldvalues are added into the LocationsProducts table. Works fine.

However there is one problem.

When I am adding a record to the LocationsProducts table and the UniqueName field (FieldName is Code in the pictures) validates, the script performes a search in the Products table. When it does not find a corresponding record I want to add a description (omschrijving) and price (Stuksprijs) manually. When we return to the orignal layout I find the portal sorted with the Description Field active in the WRONG record.

See attachment wrong.png

Is there a way to correct this so I am getting the right field in the right record active?

See attechment right.png

I hope to hear from you

[comment]

I think the workflow should be something like this: first, user selects a product to add to their location. At this point you need to validate that the product has not been already added to the location (i.e. the combination LocationID | ProductID is unique in the join table). If the validation was successful, the join record is created and the product code and description can be looked up from the Products table.

Next, user enters or modifies the local code for the product. Here, you need to validate that the combination LocationID | Code is unique.

Both validations can be preceded by a script, using the same method as shown in my demo. There is no need for the script to perform a find; on the contrary, the script cannot perform a find in the LocationsProducts table unless the record in Location (including the join records in the portal) is committed. Committing the record will cause the portal to sort itself.

[Adri Oosterwijk]

Hi Comment,

I feel so stupid but for some reason I am unable to get it to work. Is the join table a real table or just a TO?

My general Items file has a relationship with another table (license) so the General Items can be added at the top of the chain so to speak.

I have set up the relationships in several different ways (even with an extra TO GeneralItems 2) but one way or the other it will not work as I want (or as you described). Can it be caused by the fact that my data is separated from my app (UI and logic) file? I am unable to create a lookup. I am unable to make an unique relationship. I was able to set the portal up to show the correct records but at that point I was unable to add records when they didnot exist in the General Products table. The portal was only showing the field values from the General table and that is not what I am aming for. Short.... I'm lost.

Can you provide me with an example wich I can study?

I'm sure that it will be very helpfull.

Thank you very much.

[comment]

Is the join table a real table or just a TO?

It's a real table.

Can it be caused by the fact that my data is separated from my app (UI and logic) file?

That is quite possible.

Can you provide me with an example wich I can study?

Not at the moment - perhaps later. But it won't be separated.