Relationship Question

[Database Administrator]

FM8.5

I have CONTACTS and INVOICES tables in my Database Definition. They
are related through a Kp_ContactsID and a Kf_ContactsID. This
relationship allows me to create a portal on my Contacts form that
shows information from my Invoices. The whole relationship line is
CONTACTS, INVOICES, LINES, PRODUCTS.

I created a new table called INVOICES_EURO and a relationship with the
CONTACTS table. Currently this relationship allows me to enter billing
information (Contact's info) on an Invoice.

The INVOICES_EURO relationship is in fact based on a Table Occurance
called CONTACTS_EURO, the later being based on the CONTACTS table. I
cannot direct a portal to show related records from INVOICES_EURO on
my Contact Form View.

Any thoughts on why?

Thanks
Matthew

[Database Administrator]

Buckbuck wrote:
> FM8.5
>
> I have CONTACTS and INVOICES tables in my Database Definition. They
> are related through a Kp_ContactsID and a Kf_ContactsID. This
> relationship allows me to create a portal on my Contacts form that
> shows information from my Invoices. The whole relationship line is
> CONTACTS, INVOICES, LINES, PRODUCTS.
>
> I created a new table called INVOICES_EURO and a relationship with the
> CONTACTS table. Currently this relationship allows me to enter billing
> information (Contact's info) on an Invoice.
>
> The INVOICES_EURO relationship is in fact based on a Table Occurance
> called CONTACTS_EURO, the later being based on the CONTACTS table. I
> cannot direct a portal to show related records from INVOICES_EURO on
> my Contact Form View.
>
> Any thoughts on why?
>
> Thanks
> Matthew


Contact:InvEuro will be CID::CID; is your ID field in InvEuro
indexable?. Is it the same format (text or number) on both sides of the rel.





A separate InvEuro table does not sound like a good idea design wise.
Why not just have one table (invoices), and create a secondary key
(DefaultCurrency, Location...), if you need filtered reporting, totals etc.

[Database Administrator]

On Jul 16, 4:28*pm, Chris Brown
wrote:
> Buckbuck wrote:
> > FM8.5
>
> > I have CONTACTS and INVOICES tables in my Database Definition. They
> > are related through a Kp_ContactsID and a Kf_ContactsID. This
> > relationship allows me to create a portal on my Contacts form that
> > shows information from my Invoices. The whole relationship line is
> > CONTACTS, INVOICES, LINES, PRODUCTS.
>
> > I created a new table called INVOICES_EURO and a relationship with the
> > CONTACTS table. Currently this relationship allows me to enter billing
> > information (Contact's info) on an Invoice.
>
> > The INVOICES_EURO relationship is in fact based on a Table Occurance
> > called CONTACTS_EURO, the later being based on the *CONTACTS table. I
> > cannot direct a portal to show related records from INVOICES_EURO on
> > my Contact Form View.
>
> > Any thoughts on why?
>
> > Thanks
> > Matthew
>
> Contact:InvEuro will be CID::CID; is your ID field in InvEuro
> indexable?. Is it the same format (text or number) on both sides of the rel.
>
> A separate InvEuro table does not sound like a good idea design wise.
> Why not just have one table (invoices), and create a secondary key
> (DefaultCurrency, Location...), if you need filtered reporting, totals etc.

I do not understand your suggestion. Can you flesh this out a bit.

Thanks

[Database Administrator]

Buckbuck wrote:
> On Jul 16, 4:28 pm, Chris Brown
> wrote:
>> Buckbuck wrote:
>>> FM8.5
>>> I have CONTACTS and INVOICES tables in my Database Definition. They
>>> are related through a Kp_ContactsID and a Kf_ContactsID. This
>>> relationship allows me to create a portal on my Contacts form that
>>> shows information from my Invoices. The whole relationship line is
>>> CONTACTS, INVOICES, LINES, PRODUCTS.
>>> I created a new table called INVOICES_EURO and a relationship with the
>>> CONTACTS table. Currently this relationship allows me to enter billing
>>> information (Contact's info) on an Invoice.
>>> The INVOICES_EURO relationship is in fact based on a Table Occurance
>>> called CONTACTS_EURO, the later being based on the CONTACTS table. I
>>> cannot direct a portal to show related records from INVOICES_EURO on
>>> my Contact Form View.
>>> Any thoughts on why?
>>> Thanks
>>> Matthew
>> Contact:InvEuro will be CID::CID; is your ID field in InvEuro
>> indexable?. Is it the same format (text or number) on both sides of the rel.
>>
>> A separate InvEuro table does not sound like a good idea design wise.
>> Why not just have one table (invoices), and create a secondary key
>> (DefaultCurrency, Location...), if you need filtered reporting, totals etc.
>
> I do not understand your suggestion. Can you flesh this out a bit.
>
> Thanks



I read your original as having 2 separate Invoice TABLES (not table
ocurrances): Invoices, and Invoices_EURO:

<<
I created a new table called INVOICES_EURO
>>


So if you do have a separate Invoices_EURO table, and you want a portal
on Contact form layout, to show all invoices from table Invoices_EURO:

<<
I
>>> cannot direct a portal to show related records from INVOICES_EURO on
>>> my Contact Form View



the rel would be ContactID::ContactID; if this is not working then check
the right side rel key field definition (ie. ContactID , that it is the
same format as the actual Contact table field (e.g. text), and that it
can be indexed.


If you do have 2 separate Invoice actual tables, This seems
unnecessarily redundant/duplicative. Using just one actual data table
for all invoices (US, Euro...) perhaps create a key in Contacts table to
indicate default currency for the (each) Contact. Then calculations in
Invoices table can reference the Contact record to evaluate the Contact
Currency, and return the appropriate result.

So invoice total (for the purposes of invoice sent to Customer is
calculated by something like:

Sum(invoiceLineItems) *CurrencyConversion



where if your default currency (eg US) CurrencyConversion =1 if Customer
= Euro an appropriate conversion factor is applied.