Weird Value List in Portal ... driving me nuts!

[ron G]

I created three tables. I put a popup value list related to the third table. Sometimes it works. Sometimes it doesn't. Sometimes it shows actual data. Sometimes it shows weird data. Sometimes at the end of the portal row, no new, blank row is added below.

It is late and I am going bonkers over this!! (perhaps time for bed)

What I want it to do is:

#1)Display the Vendor Number and Name for the user to select from.

#2) Put the vendor name in the field.

Am I asking too much?

As is, the popup doesn't work at all. Why? Beats me! It worked a few minutes ago; not elegantly but it did work. I checked to make sure the fields were not 'infringing' on outside their place in the top row. So, I am clueless.

Any help is appreciated. Thanks much.

Ron :B

Basis.fp7.zip

[LaRetta]

1) In the Vendors portal on your Main layout, disallow entry into the PK_Vendor_ID. Let people only type into the Name. In fact, the ID doesn't need to be shown there if 'Allow creation' is on. A new record is created ONLY by typing into a vendor field OTHER THAN the ID. Include more than the name in that portal (Like city, state) in case there are two Brown Company's in your vendor list.

2) Change your popup in the Improvements portal to only have the fk_Vendor_ID from the Improvements table (same as portal) and NOT from the Vendors portal, using the PK_Vendor_ID.

Now about Item #1 ... if you are allowing 'Allow Creation' in the Vendor table from that Main layout then you don't need a Vendor Add button. If you plan to add a vendor via script and going to the vendor table, then you don't even need the vendor portal. Now about Item #2 ... if you plan on having a vendor portal then you don't even need the popup because Users can select from the portal - or vice versa.

It doesn't feel like you have clarity on the best way for this process to function. How best to present the data for USE is something all Developers struggle with.

[LaRetta]

By the way, I realize you are just starting with this particular design, but since you allow Users to go to your various tables and create new records, remember to DISALLOW ENTRY into all ID fields. That includes that Improvements portal with the ImprovementID (in fact, don't even display it - it wastes space). Otherwise, a User will change those IDs (even if only by mistake). :wink2:

Edited October 6, 2007 by Guest

[Søren Dyhr]

Since I find it a little tough to keep track of LaRettas suggestions, do I however think she means something in this direction.

--sd

BasisMod.zip

[ron G]

Thanks for the ideas.

Yes, I am 'new' to FM but what you see is more a 'proof of concept than a finished design. Since I am still feeling my way in the dark, I put the ID fields in my portal so I can see how things are working. The finished portal would have no such ID's.

The portal is to be used to make 'vendor payments' and one field is the vendor Name. The ADD button is there because sometimes, like on startup, there are no Vendors.

I will study what you have said and probably learn a great deal.

I "*really appreciate*" your ideas. Thank you verey much!

Ron

[ron G]

1) In the Vendors portal on your Main layout, disallow entry into the PK_Vendor_ID. Let people only type into the Name. In fact, the ID doesn't need to be shown there if 'Allow creation' is on. A new record is created ONLY by typing into a vendor field OTHER THAN the ID.

[color:blue]I am somewhat confused by this recommendation. The NAME field is a popup that shows the Vendor ID and the Vendor Name but adds the Vendor Name when a selection is made. It does not work. Also, the portal does not add a new row . (related problems?)

2) Change your popup in the Improvements portal to only have the fk_Vendor_ID from the Improvements table (same as portal) and NOT from the Vendors portal, using the PK_Vendor_ID.

[color:blue]I did this and it doesn't seem to make a difference. The popup still does not work. Question: "Why is a important to use the Improvements::FK_VendorID and not Vendors::PK_VendorID. Shouldn't they be the same value? In any event, using the FK_VendorID didn't make a difference. The popup still does not popup.

Now about Item #1 ... if you are allowing 'Allow Creation' in the Vendor table from that Main layout then you don't need a Vendor Add button. If you plan to add a vendor via script and going to the vendor table, then you don't even need the vendor portal. Now about Item #2 ... if you plan on having a vendor portal then you don't even need the popup because Users can select from the portal - or vice versa.

[color:blue]I added the ADD button because sometimes a vendor is not in the vendor table. I want to track vendor specific... address, phone, city, state, account, terms etc so that information is available as a report AND I want to be able to produce transactions by vendor which is the main input in the portal. To that end I have a separate table for vendor information and popup which returns vendor name... which does not work for some reason.

It doesn't feel like you have clarity on the best way for this process to function. How best to present the data for USE is something all Developers struggle with.

[color:blue] Yep, I am struggeling but from the struggle comes clarity and progress. Thanks for your help!

[LaRetta]

[color:brown]Well, lets work through this then ...

1) In the [color:brown]Vendors portal on your Main layout, disallow entry into the PK_Vendor_ID. Let people only type into the Name. In fact, the ID doesn't need to be shown there if 'Allow creation' is on. A new record is created ONLY by typing into a vendor field OTHER THAN the ID.

[color:blue]I am somewhat confused by this recommendation. The NAME field is a popup that shows the Vendor

[color:brown]No it isn't. Re-read it ... I am talking about the VENDOR portal not the Improvements portal. There is no popup on the Vendor name in the vendor's portal.

2) Change your popup in the Improvements portal to only have the fk_Vendor_ID from the Improvements table (same as portal) and NOT from the Vendors portal, using the PK_Vendor_ID.

[color:blue]I did this and it doesn't seem to make a difference. The popup still does not work. Question: "Why is a important to use the Improvements::FK_VendorID and not Vendors::PK_VendorID.

[color:brown]See attached. You must insert the ID into the field. The field must be the field in Improvements - you had it as the FIELD FROM YOUR VENDORS TABLE. To make the relationship, you must have the fk_vendor_ID in Improvements - and have it match to pk_vendor_ID in Vendors. You had the ID FIELD from Vendors in your Improvements table! It could NOT work!

[color:blue]I added the ADD button because sometimes a vendor is not in the vendor table. I want to track vendor specific... address, phone, city, state, account, terms etc so that information is available as a report AND I want to be able to produce transactions by vendor which is the main input in the portal. To that end I have a separate table for vendor information and popup which returns vendor name... which does not work for some reason.

[color:brown]Understood. Then if that is the case, you do NOT need 'Allow Creation' turned on to the Vendors table (in your relationship graph) because you always click the button which takes you to the Vendors layout the add all the vendor information. That was my point about not needing both processes for creating a vendor.

Simply, you must insert the vendorID into a FIELD IN IMPROVEMENTS (the FK_Vendor_ID field) but select from the name (using the popup as attached). Check out the attached file now, re-read my suggestions and step through the logic. The logic is there ... truly it is ... whether Soren sees it or not. You're doing great, Ron!

LaRetta :wink2:

BasisREV1.zip

[LaRetta]

By the way, if when you create a new Vendor by going to the other layout, you then return to Main but don't see that new Vendor listed in the Vendor portal, it is because the vendor can ONLY exist to a record from your main layout (Properties) after you have added it in the Improvements portal (you can't create a grandchild until you create a child). You will see it automatically appears in the popup in Improvements.

Once it is selected and added as a record in Improvements, you will still have a refresh issue of your Vendors portal. I've stumbled across a few tricks which have worked in various situations but I usually just script and FORCE it to work by attaching script directly to the popup with something like:

Go To Field [ Improvements::fk_Vendor_ID ] ... which allows User to select the Vendor

Pause/Resume [ 1 second ]

Refresh Window [ Flush join cached results ]

However, I see you are Mac. And you will NOT want to use script on your popups. Maybe others have a method of forcing a refresh. I suppose you can also attach a button next to your Vendors which says REFRESH. In truth, it will display the new Vendor as soon as you de-select or click on the layout anyway (after entering it in Improvements) ... but it can be irritating, I agree.

LaRetta

[LaRetta]

Well silly me ... you can attach a different table occurrence of your Vendor's portal. Attach vendors directly to Property using cartesian join. Then, all vendors will show regardless. You don't NEED vendors filtered according to your Improvements table!

If you join Vendors to Properties on cartesian 'x' operator in your graph (maybe name that table occurrence ALL VENDORS), join any two fields (except container or global) then it will refresh because it won't be dependent upon Improvements at all. Be sure to change the portal name and ALL fields within that vendor portal to the new table occurrence name.

[ron G]

[color:brown]No it isn't. Re-read it ... I am talking about the VENDOR portal not the Improvements portal. There is no popup on the Vendor name in the vendor's portal.

[color:blue]Maybe we are seeing two different things. I don't have a Vendor's portal. I have a portal on the Main Layout that is references the IMPROVEMENTS table as it's main focus. Is that what you are calling "Vendor's Portal"? (See jpg above)

Also, the NAME field on the portal is setup to be a popup and it doesn't work or works with weird results. (Which tells me I have a MAJOR misunderstanding on how it should work! Right now it doesn't work) /color]

I am still working through the rest of what you said but wanted to clarify this point first.

Thanks for your help.

Ron

[LaRetta]

Ron, use my file I provided. Did you try to select vendor (in the IMPROVEMENTS portal) from my file because it works right.

Also, the vendor portal is on the same layout - just expand the layout wider ... I had no idea you weren't even using the vendor portal since it exists directly to the right of the Improvements portal.

Again ... from my file, select the fk_vendor_id field in the Improvements portal. This will insert the fk_vendor_id but display the vendor name. This completes the relationship from:

Improvements::fk_vendor_ID = Vendors::PK_Vendor_ID

... and the vendor is inserted into Improvements. It also allows selection of a new line (because 'allow creation' is on) within that same Improvements portal.

Edited October 6, 2007 by Guest

[ron G]

Ron, use my file I provided. Did you try to select vendor (in the IMPROVEMENTS portal) from my file because it works right.

[color:blue]OOPS. That portal was just one of my 'experimental' portals to see how/if it would work. Not intended for the 'real' application. I am sorry that I mislead you. Like my dad used to say As I got older, he got smarter. (or so it seemed to me)

I am eager to make the changes you saw.

Thanks again!!

Ron

Improvements::fk_vendor_ID = Vendors::PK_Vendor_ID

... and the vendor is inserted into Improvements. It also allows selection of a new line (because 'allow creation' is on) within that same Improvements portal.

[ron G]

Edited October 7, 2007 by Guest

[LaRetta]

Hi Ron,

Your file isn't quite right yet ...

1) Select your fk_vendor_ID in Improvements table. Select from menu - Format > Field Control > Behavior. And check 'Allow field to be entered in Browse mode.'

2) Then, fix your value list again ...

Select your Vendors value list. Use values from first field in VENDORS not Improvements. Select the pk_vendor_ID. Also display from vendors the name.

Then say OK (and OK to back out of value lists) - everything else is fine. In your graph, you joined thus:

Improvements:fk_vendor_ID = Vendors::pk_vendor_ID

Now picture your Improvements portal. The fk_vendor_ID in Improvements must match the pk_vendor_id in Vendors. If it does, you have a match. But where do you get the proper list of ALL vendor IDs and their names? Only from the vendor table itself - using all vendor values. So your value list uses all vendors. And that value list is attached to the Improvements::fk_vendor_ID field so it inserts an ID value into it and makes the match which makes the relationship work.

You should be good to go now! :smile2:

[ron G]

Excellent explanation! Thanks for the clarity.

Ron