Is a portal the best solution?

[ron G]

I have an application with a STOCKS table.

Stocks Table is composed of:

stockid.pk

stock_symbol

stock_name

I have a layout based on stock.

On that layout I have a tabbed interface.

In a tab I want to have way for the user to add a new STOCK.

I tried duplicating the Stocks table with Stocks2 and setting a relationship between them using stockid.pk (with allow addition and deletion turned on on STock2).

[color:red]

But, when I add a portal to the tab and base it on Stock2 I get only a single stock entry and not the list of existing stocks.

Got any ideas?

Thanks

[efen]

Is it a cartesian join [with the X]? If it is only a = join then it will only show one record

Edited July 13, 2009 by Guest

[ron G]

Yes using '≠' or 'x' instead of '=' results in a portal showing all the stocks.

But, using '≠' or 'x' results in the relationship option "Allow creation of records...." being disabled. Therefore, I can not ADD new records to the portal.

Is there a way around this?

Thanks

Ron

[efen]

button next to portal with script to create new record?

[ron G]

The problem with the button idea is that the portal can not be entered in a new row, so I must navigate to a window with a different layout in it.

If I was going to do that, why bother with a portal in the first place?

Thanks for thinking about this problem.

ron

[efen]

But why would you create a new record in the portal if you couldn't enter any new stock item in it?

Edited July 13, 2009 by Guest

[ron G]

I need the portal to both display existing stock symbols and their associated names AND allow the user to ADD new stocks symbols and stock names.

[efen]

I don't understand what you're trying to achieve - if the fields in the portal can't be entered how can you "ADD new stocks symbols and stock names" there without going to another layout/window?

As far as I know (without a lot of work?) you can't have some rows in a portal with the fields not enterable and in a new row the fields can be entered

[Fitch]

Ron, have you considered using a list layout for this rather than a portal?

[ron G]

A list layout would work but I am trying to put a portal on a tab. The other tabs are related to the Layout table=== stocks. I need a portal that also relates to stocks...

[Fitch]

If you want to add a new Stock, and your layout is based on Stock, then all you have to do is make a new record, which can be done by menu command or script.

If your portal is based on a self-join between Stocks and another TO of Stocks using a Cartesian join, the new record will appear in the portal and you should be able to edit it there.

IOW, you won't use the "Allow creation of records" property of the relationship to create a record directly by entering data in an empty portal row. Instead, you'll create the record first, then edit it.

[ron G]

I got it to work by putting a calculation field (field1) in STocks and then doing a self join. The portal was based on stocks2 and it works great.

In implementing this process another 'weirdness' showed up. In the Options table I have fields:

optExpirDate(Expiration Date) and OptSymbol (Symbol). [color:red]I put Options::optExpirDate on the OptionTransactions portal.

When I go into OptionTransactions and enter the appropriate option symbol I expected that corresponding Options::optExpirDate would be brought over from the Options table. It doesn't. What am I doing wrong?

Thanks

[Fitch]

You need another TO of Options, linked to OptionTransactions. Put a field from this TO in the portal.

[ron G]

I have FM 10 Advanced

There is a table called Stocks

It i self joined to STocks2 via a calculated field called FIELD1 which is set to 1. (So every record relates to itself via this constant)

Now for the 'weirdness'

I put Stock2 in a portal on the layout based on STock. My goal is to allow for the addition/editing and deletion of STOCK records.

(This used to work but I screwed it up somehow)

Weirdness #1: I open a second window and put Stock layout in it.

I goto back to the STock layout portal add a new record to the portal and a as I type into the 2nd field on the portal, a blank row appears in the Window containing STock. Huh?

If I complete the record, a new blank row appears at the top of the portal. Why?

Weirdness #2: On each portal row there is a 'delete portal row' button. After adding several records, if I push the delete button it deletes ALL records on the portal?

In general, this thing is just weird. I suspect I don't have my relationships correct. To that end I have tried all the combinations of Allow Addition and Allow Deletion I can think of. Also, there are no scripts running.

I am out of ideas so I thought I would turn this over to someone who, hopefully, has run across this type of thing before.

Thanks for reading...

[ron G]

As an act of desperation, I linked the 2 tables with a cartesian join and manually added new records. Worked great!

However, to facilitate an quick and easy deletion, I put an "X" button on the end of each row. Clicking that button was directed to: delete portal row or delete record.

Either way, it ALWAYS DELTED ALL THE RECORDS IN STOCKS!! YIKES!

Got any insight on that weird happening?

[LaRetta]

In general, this thing is just weird. I suspect I don't have my relationships correct.

Correct.

(So every record relates to itself via this constant)

No, every record won't related to itself via this constant ... every record will relate to EVER RECORD in the table, via this constant. For a record to relate to itself, it must be joined by its unique ID.

Some basic understandings:

1) For Allow Creation to work (and also to allow deletion of a single stock record displayed in the portal), the relationship to the portal must be based upon StockID. This is because Allow Creation will set the 'join field' into the related table but only if it does not currently exist. So you are trying to insert a '1' into the same table. But it also is a calculated 1 on both sides so it can't insert the 1 (you would receive message saying the field was not modifiable).

2) To display ALL records in a portal, you don't need a field with a 1 in it ... you simply need a cartesian join (which in the graph connectors, is the bottom one - the X). For a cartesian join, any two fields can be joined (they do not have to related). It can be any fields except globals or containers (or calculations with either).

But why are you attempting to create a record for the current table through a portal and not simply creating a new record in your main table where you stand, Ron? And to delete a record, you should be viewing the record anyway so just delete the record you are viewing (or go there first and then delete it).

Is there more here that you haven't yet explained on why you are doing this? :wink2:

[LaRetta]

I also see that you had already solved this situation here . Please resolve your issues on one thread instead of opening new threads. ;)

Edited July 19, 2009 by Guest

[Lee Smith]

Topic Merged!

[ron G]

Thank you for the explanation of "X" cartesan (sic) joins. I guess they are out as a solution.

Some basic understandings:

1) [color:red]For Allow Creation to work (and also to allow deletion of a single stock record displayed in the portal), the relationship to the portal must be based upon StockID. This is because Allow Creation will set the 'join field' into the related table but only if it does not currently exist.

[color:blue]I deleted the X and replaced it with = between Stock::StockID.pk and Stock2::StockID.pk. I checked Allow Creation and Allow Deletion for both tables. As pictured above, when I add records everything seems fine until I click out of the portal then I get a blank record at the TOP of the portal. I checked for sorting anomalies and don't see anything.

2) To display ALL records in a portal, you don't need a field with a 1 in it ... you simply need a cartesian join (which in the graph connectors, is the bottom one - the X). For a cartesian join, any two fields can be joined (they do not have to related). It can be any fields except globals or containers (or calculations with either).

[color:blue]I need to display/add/edit and delete records in the portal. So, I guess the cartesian join is out?

But why are you attempting to create a record for the current table through a portal and not simply creating a new record in your main table where you stand, Ron?

[color:blue]I could go to STOCK in a different window and do what I am trying to do. But, I am stretching myself to understand the capabilities and limitations of portals in this 'self join' situation.

And to delete a record, you should be viewing the record anyway so just delete the record you are viewing (or go there first and then delete it).

[color:blue]While viewing a paritcular Stock record, if I click "DELETE RECORD" all the records in the portal are deleted? I wonder if this has anything to do with the 'blank row' the portal adds at the top?

Is there more here that you haven't yet explained on why you are doing this?

Edited July 19, 2009 by Guest

[comment]

cartesian

cartesan (sic)

:qwery:

http://dictionary.cambridge.org/define_b.asp?key=sic*1+0&dict=A

[ron G]

Here is the latest. As pictured above, I instituted a cartesian relationship between Stocks and Stocks2. Using a button to Add and one to "X" delete rows works. Good.

However, [color:red]when I go to the "Add Options" tab and click on a blank portal row it ADDS A BLANK ROW TO STOCKS!!!

Is that a byproduct of the cartesian relationship?

stockoptionsfixed.fp7.zip

[LaRetta]

Ron, I have deleted your new post (identical) on this very subject. You have been asked NOT to double post. It is against Forum policy. Double posting wastes everyone's time, it confuses the issues, and it wastes forum resources. Do not do it again.

Edited July 20, 2009 by Guest

[Fitch]

You should turn on your debugger. You have a script trigger that is making the new record.

[ron G]

Absolutely correct. Once again, it is MY error. Thank you very much.

However, there is this mystery: (and there are no scripts running)

When I go into Add Stock, when there are no stocks listed, after I add the first symbol and name and then click out of the portal, a blank row is added at the top of the portal. This blank row is seen as a 'new record'. If you have any insight on what causes this I would really appreciate hearing about it. This one has been frustrating me for a loooooooong time.... thanks

I think I got it. I simply use a Cartesan join. Why does this work? I will have to figure that out....

stockoptionsfixed2.fp7.zip

Edited July 21, 2009 by Guest

[Fitch]

When a relationship is set to allow creation of new records, you'll see what appears to be a blank row at the bottom of the portal. I.e. the "delete" button appears even though there is no related record there. This is normal and the only workaround is to turn off allow creation for that portal's relationship, and create related records some other way.

If your relationship or portal is sorted by a field that you have left empty, the related record will sort to the top.

Is either of those things what you mean?

[ron G]

Your explanation is absolutely correct. Unfortunately it does NOT describe my situation. See post 336292.

In addition to the normal 'blank row below' a new record, I get a blank record above...? For some reason the situation 'normalized' when I established a cartesian link between the related records. Now I just have to figure out why it worked.

Thank you for your thoughts and help.