Write a script that captures the ID of the one you want to mark. Then loop through all the others and set their flags to "".
I used a case statement in a field calculation, it woks in a fasion but get a "?" instead of a "1"
I created the preffered_flag as a calculation field with the cauculation "
product::prefered_supplier = supplier::name;
As is is unstored it does recalculate and is only in 1 field, just not sure why get a "?"
When you set a calculation, you don't need to insert the name of the field you're calculating. Your calculation should read:
product::prefered_supplier = supplier::name;
(Note I changed it from "Y" to "1", since that's what you said you wanted. You can change it back if that's different than what you need.)
So ill assume a few things which u can correct me of I'm wrong.
U have many products and many suppliers. When the smoke clears, ur suppliers table will have a single preferred provider for each product (unless that product doesn't yet have a preferred provider).
Painting a picture , u might have 30 products , 110 suppliers. 30 of those 110 will be the preferred providers and the remaining 80 are the second string which may get the call as a backup but lost out to a colleage.
Unless u have a specific need to flag the preferred provider field with a 1, switch it up and flag it with the Id of the product. This way, u can derive from the existence of a value in this field , that the provider is a preferred, and u can also derive exactly which product it is a preferred for. If its just a 1, u have no way to specifically link it to a product.
To select from a list, create a value list using as the source - the id of the provider. U can also check to have it display a second field which u can set to name. If u tell it to hide field one , the selection list will just show names of providers but u will use a popup field selector so despite seeing the name from which u select, the value that will be used behind the scenes will be the id. U can put a on modify script trigger on the popup selector that setfields the product id into the providers tag field (a few ways to write the script).
The results being , every time u select a provider it will push the product id over into that providers record.
In terms of making sure that it is the provider to have the id of the product in its tag field can be handled a few ways. What comes to mind would be to do a validation step in ur script prior to setting the field. This may be handled better by someone else suggestion but I would prob grab a list of the preferred providers ala List (preferredProviderfield) and use patternCount to see if u already have this id in the list, if u don't - u r free to proceed. If u do get a hit back on pattern count , u will need to blow it out which again , could be done a few ways. The other option would be not to worry about more than one preferred being set into the field but instead , add a timestamp to the mix, either in its own field or concat'd to the id so u could check for the most recent timestamp in later uses of the flag.
Ill stop and leave the last parts details to ur gutcheck on the first half, then wrap back to clean it up.
Best , Steve
I just typed "Y" on auto pilot .. I used a "1"
Thank you, I did't think I was far off but still getting used to calculation logic.
Mike gave me the answer I needed, but just to give you an idea of what Im doing. When a product is created and is set to "active" it "must" have a prefered supplier attached (has option to create via script if non existant) otherwise it does not need a supplier attached. On the order form a product is chosen and the prefered supplier is marked ( although this is not the end, there is alot more logic befor the order is placed)
EG: Checks dilivery days, Cut of order times, Status Change ( if you need to order a "yellow widget" from your preffered supplier for next day delivery, but have missed order cut of time the order will flag this and give option to change supplier.
My impression is that you need a join table btw products and suppliers, since it is a many-to-many. Don't products have many suppliers (and one is preferred) and suppliers can be the source of many products?
I would have a portal of suppliers on a product form (portal is the join table). Next to each supplier's name, I would have a checkbox, dsp_flag_primary_c. Clicking the checkbox sets the key supplier ID in the product::supplier_primary_ID field. The dsp_flag_primary_c = 1 if the supplierID = product::supplier_primary_ID.