Portal, layout or relationship

[crazybake]

I am struggling with something that should be simple, but cannot figure it out.

Below are three tables: Contacts, MaterialsTransactions, and MaterialsTransactionLineItems

Within a layout showing records from the Contacts table, I created a portal showing records from MaterialsTransactionLineitems (so contacts could see each item and not just each transaction)

 Layout = Contacts Table; Portal = MaterialsTransactionLineItems

The field on the left is MaterialsTransactionLineItems::Transaction_fk; the field on the right is MaterialsTransactions::TransactionDate

Given the relationship, why is the date not showing correctly? Instead, just showing first record for all? Is it the relationship, or the way I have created the portal... both?

Thanks

Edited October 21, 2017 by crazybake
Spelling

[Steve Martino]

I don't quite understand what your tables do, but if you line items table is an atypical line items table, it should be a join table between Transactions and a Material table.

Also the contacts table seems to be connected to the wrong foreign key.  I only say that because it's showing a many to many relationship. 

Edited October 21, 2017 by Steve Martino

[crazybake]

Steve,

yes, I didn't show an additional table, Materials, that is a join table. Not sure what is wrong with the foreign key. The Student ID field is a number connected to Contact_fk, a number, in the MaterialsTransactions table...

 

[Steve Martino]

Is the Student ID field set up as an auto-enter serial number?  Are both key either set as text or numbers.

[crazybake]

No auto enter, and both are numbers.

[Lee Smith]

Hi crazybake,

1 hour ago, crazybake said:

No auto enter, and both are numbers

I'm not understanding your schema either. How about attaching a copy or a mockup of your file?

See more information about how to attach a file here, Anatomy of a Good Topic when contains confidential data.

Lee

[crazybake]

Attached is a sample database, with non-related tables and records removed. It is still a bit hefty, but tried to pare it down for quick review. Guest access is enabled.

When you click on students, I added a red box where you can add a transaction. This is where I am having the issue. A transaction date will be added, but when you close this window, I cannot get the date to show up in the student portal.

thanks

 

WTC_v_1_2017-18_TEST2.fmp12

Edited October 23, 2017 by crazybake

[Lee Smith]

User and Passwords?

 

[crazybake]

I enabled guest access if you select "sign in as guest" button

Edited October 23, 2017 by crazybake

[bruceR]

1. Try skinnier padding.

2. Your script doesn't check for empty Student ID.

3. Change the graph to add a CREATE relation

Edited October 23, 2017 by BruceR

[crazybake]

Thanks Bruce,

I am not able to download you example, it errors out.

... I guess my understanding of relationships needs work, can you explain why I would require another join table like CREATE, when the relationship between MaterialsTransactionLineItems::Transaction_fk and MaterialsTransaction::TransactionNumber has already been established.?

Edited October 23, 2017 by crazybake

[Lee Smith]

Me either?

[bruceR]

Here it is again (hopefully).

Reason for CREATE relationship:

The existing relationship leave a non-functioning ghost bottom row in the portal.

WTC_v_1_2017-18_TEST_ModBFR.fmp12.zip