1 of 1 people found this helpful
I think you need only 2 tables:
You might have several banks and several bookmakers... They can be in the same table... classified perhaps by type... Bank / Bookmaker perhaps or maybe BM / Bank.
Whatever you choose... the same value can be auto-input in the transactions table in some circumstances. (I would shed the bit that says XYZTransaction as when they are in a table called transaction)
You can have many relationships with a single table, including self-relationships. You could for eg. have a global field with calculated value of 'BM' and create another relationship instance called "BMTransaction" where the global is matched to the type field... then a layout which does not use the base table but rather that instance. You would have to repeat the exercise for the Bank.
Alternatively you could have that same global field which is a popup with valuelist assigned with values of BM and Bank... and match that with Type... and with one layout show only BM or Bank by changing the selection in the global. NB... this all takes place in the transactions table.
To get the Institution portal to the transactions to auto-enter either BM or Bank, you would make a relationship to the Transactions where both the IDs AND the type are used in the relationship from Institution to transactions.
I hope this makes sense... it is late.
Thank you for taking the time to repsond to my question.
Its obviously difficult to provide all information required to get a fully informed response. I can fully understand your suggestion that there should be only one table (for bookmaker and Bank) but in this instance, the amalgamation of Bookmaker and Bank tables would only provide further issues as in my application is very much centred around the Bookmaker table, Bank is periphery to this. There are actually few attributes shared between these two entities, as such, it may make sense to make the amalgamate the data due to limitations of FM (in my opinion) but from a pure design perspective they should not be a single entity.
Your point around the use of global fields is very useful and I think I will play around with this at the transaction table level, I've think the use of global fields or creating a parent table (type) and use portals to view transaction would solve my problem.
Many thanks, my knowledge of FM continues ot evolve, hopefully I'll be able to respond to others in time.