Jump to content
Sign in to follow this  
Anuviel

Relationship "wall"

Recommended Posts

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 by Guest

Share this post


Link to post
Share on other sites

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 by Guest
ps:

Share this post


Link to post
Share on other sites

Thank you - I understand it now.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.