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, 20178 yr by crazybake
Spelling

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, 20178 yr by Steve Martino

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...

 

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

No auto enter, and both are numbers.

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

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, 20178 yr by crazybake

User and Passwords?

 

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

Edited October 23, 20178 yr by crazybake

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, 20178 yr by BruceR

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, 20178 yr by crazybake

Me either?

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