Hi all,
I have created a diagram that shows how the API Tables reference each other.
- This is an “unofficial” diagram.
- I would say its 95%+ accurate, so there maybe a couple of errors.
- Tables and Fields are based on Infusionsoft Table documentation.
- Text/String fields are set to TEXT type, as the limits are unknown.
- I will provide the files and SQL output later. Need to see what other tweaks are needed.
- Making this diagram has revealed additional things that could aid developers, which will be explained in a later date.
- Ticket tables are not included, as the module is deprecated from Infusionsoft.
Click on the image or the link below to enlarge it. It is quite large at 4795 × 5149 pixels!
Table Relationships
List of the table relationships are also present on the diagram on the bottom left of the picture.
Affiliate
Affiliate.ContactId > Contact.Id
Affiliate.ParentId > Affiliate.Id
Referral.ContactId > Contact.Id
Referral.AffiliateId > Affiliate.Id
Company
Company.CompanyID > Company.Id
Company.LeadSourceId > LeadSource.Id
Company.OwnerID > User.Id
Contact
Contact.CreatedBy > User.Id
Contact.LeadSourceId > LeadSource.Id
Contact.OwnerID > User.Id
Expense.ContactId > Contact.Id
FileBox.ContactId > Contact.Id
Contact Action
ContactAction.ContactId > Contact.Id
Credit Card
CCharge.CCId > CreditCard.Id
CCharge.PaymentId > Payment.Id
CreditCard.ContactId > Contact.Id
Custom Fields & Tabs
DataFormField.GroupId > DataFromGroup.Id
DataFormGroup.TabId > DataFormTab.Id
Follow Up Sequence
Campaignee.CampaignId > Campaign.Id
CampaignStep.CampaignId > Campaign.Id
CampaignStep.TemplateId > Template.Id
Lead / Opportunity
Lead.AffiliateId > Affiliate.Id
Lead.ContactID > Contact.Id
Lead.StageID > Stage.Id
Lead.StatusID > Status.Id
StageMove.OpportunityId > Lead.Id
StageMove.UserId > User.Id
Lead Source
LeadSource.LeadSourceCategoryId > LeadSourceCategory.Id
LeadSourceExpense.LeadSourceId > LeadSource.Id
LeadSourceExpense.LeadSoruceRecurringExpenseId >
LeadSourceRecurringExpense.Id
LeadSourceRecurringExpense.Id.LeadSourceId > LeadSource.Id
Orders / Jobs / Invoices / Payments / Pay Plans
Payment.InvoiceId > Invoice.Id
Payment.ContactId > Contact.Id
InvoicePayment.InvoiceId > Invoice.Id
Invoice.ContactId > Contact.Id
Invoice.JobId > Job.Id
Invoice.AffiliateId > Affiliate.Id
InvoiceItem.InvoiceId > Invoice.Id
InvoiceItem.OrderItemId > OrderItem.Id
Job.ContactId > Contact.Id
OrderItem.OrderId > Job.Id
OrderItem.ProductId > Product.Id
PayPlan.InvoiceId > Invoice.Id
PayPlanItem.PayPlanId > PayPlan.Id
Product
ProductCategoryAssign.ProductId > Product.Id
ProductCategoryAssign.ProductCategoryId > ProductCategory.Id
ProductInterest.ObjectId > Lead.Id
ProductInterest.ProductId > Product.Id
ProductOption.ProductId > Product.Id
ProductOptValue.ProductOptionId > ProductOption.Id
Saved Search
SavedFilter.UserId > User.Id
Subscription / Reoccurring Orders
CProgram.ProductId > Product.Id
JobRecurringInstance.InvoiceItemId > InvoiceItem.Id
JobRecurringInstance.RecurringId > RecurringOrder.Id
RecurringOrder.AffiliateId > Affiliate.Id
RecurringOrder.CC1 > CreditCard.Id
RecurringOrder.CC2 > CreditCard.Id
RecurringOrder.ContactId > Contact.Id
RecurringOrder.LeadAffiliateId > Affiliate.Id
RecurringOrder.OriginatingOrderId > Job.Id
RecurringOrder.ProductId > Product.Id
RecurringOrder.ProgramId > CProgram.Id
RecurringOrderWithContact.AffilaiteId > Affiliate.Id
RecurringOrderWithContact.CC1 > CreditCard.Id
RecurringOrderWithContact.CC2 > CreditCard.Id
RecurringOrderWithContact.ContactId > Contact.Id
RecurringOrderWithContact.ProductId > Product.Id
RecurringOrderWithContact.ProgramId > CProgram.Id
RecurringOrderWithContact.RecurringOrderId > RecurringOrder.Id
RecurringOrderWithContact.SubscriptionPlanId > SubscriptionPlan.Id
SubscriptionPlan.ProductId > Product.Id
Tag / Contact Group
ContactGroupAssign.ContactId > Contact.Id
ContactGroupAssign.GroupId > ContactGroup.Id
ContactGroup.GroupCategoryId > ContactGroupCategory.Id
User
GroupAssign.GroupId > UserGroup.GroupId
GroupAssign.UserId > User.Id
UserGroup.OwnerId > User.Id
