Hi., I've hit a wall and am getting lost. A tip in the right direction is appreciated.

I have following set up:

Tables:

Products

Invoice_Line_Items

Invoices

Customers

They are related as:

Products to Invoice_Line_Items via Item_Code = Item_Number

Invoice_Line_Items to Invoices via PO = PO

Invoices to Customers via Customer_ID = Customer_ID

All is in order and everything works perfectly.

I would like to add one more table called Financial_Info that would hold Customers bank and payment information.

So basically once I create a new customer a record in Financial_Info would be created automatically and would pull the name, id and address leaving to enter financial info manually.

I tried relating it via Customer_ID = Customer_Id but I cannnot get it to make new records once I create them in Customer table. I check allow creation of records in the relation graph but it did not affect it.

I know that something simple if amiss however I cannot pinpoint it.

Edited July 13, 200718 yr by Guest

Hi there,

For the "allow creation of records" to work properly, the user must enter info into the portal (&/or) related field, then click out (commit) the records/field. this action will create the records, but not until adjustments have been made. If it is necessary to create a blank records in the financial area immediately after the customer records has been created, you could use custom menus in the customer area and replace the new records request with a script that would create the new records as well as call a subscript to create a records in financial that would insert the parent value.

PS: could use auto enter fields to pull the info you need from the parent record, from the script. this could also work with the allow creation idea, but you would have to enter the finacial info first before the fields would auto insert.

Edited July 13, 200718 yr by Guest
ps:

Thank you - I understand it now.

I don't understand the relationship between Invoice_Line_Items to Invoices via PO = PO. I imagine you have a script that creates an Invoice from a PO. But, InvLI should relate to Invoice by InvoiceID. I would keep two separate line item tables, POLI and INVLI, simply to allow for cascading deletes.

Have you planned to relate payments to invoices? Typically, payments are a portal in invoices (therefore getting the parent InvoiceID, which in turn gives you the CustomerID), and lets you calculate such nice things as balance due, total paid.

I'm not sure why you need another table for financial information. Sounds like just fields in the Customer Table, because I don't see the one to many relationship btw customers and financial info.

Also, I would suggest reading the White Paper on FM Dev standards. Your key field names are not consistent, and it may be confusing. Also, you're on your way to many relationships, and you might want to study the Anchor-Buoy approach to the Relationship Graph.

FM Dev Conventions

Thank you. Will look up those papers and check them out.

The reason for the PO = PO is that the line items can be created only when a PO number is created regardless of if there is an invoice number or not.

The reason for the Financial information table would be that some of our customers/clients have many different accounts, payment options and so on. So I did not want to make my customer table too big. The customer table would contain information such as name, address, terms... Financial information would contain information such as bank1, bank2, bank3, different credit card info and so on.

Anyways I got around it and have fixed it nicely. Thank you so much for the tips I will be sure to follow up on them.