Jump to content
Sign in to follow this  
JFC70

Appropriate Relationships / Data Model

Recommended Posts

Assistance Needed - Creating the right relationships based on the information below.

Being in the print, packaging, promotional and apparel industry, I am trying to build a "One Click" solution for processing client's print JOBS and invoicing them out accordingly.  Each JOB requires the following:

  • JOB NUMBER (JOB).  Maintain the same job number from initial JOB SPECIFICATION through INVOICING, and versioning of JOB NUMBER based on changes.  For example:  If I create a new JOB and it's number is 1001 and then I modify it at some point, I would like to capture the initial JOB number 1001 and then have a revised JOB number that reads something like, "1001r2".   This gives me the ability to know the historical changes along the way.  I am not sure this is possible, but any input would be great.
  • JOB SPECIFICATIONS. Build out unique specifications tied to the JOB (JOB Name, Description, No. of components that make up JOB, what are all of their specifications - Component Name, Page Count, Flat Size, Finish Size, No of Colors, What Finishing is needed, etc.  Each estimate may have different specification requirements (a brochure, an envelope, t-shirts, and a display stand require some of the same specs, but many different ones too). 
  • FORMAL QUOTATIONS to Suppliers. Once specifications are created in filemaker and verified by the client, I will need to confidentially send a formal quotation out to multiple suppliers to BID on the JOB.  These suppliers may bid the whole JOB, or just components of it.  For instance, we may have a direct mail campaign with multiple print components in it.  As a result, one supplier cannot fulfill the whole JOB.  Therefore, we will need to send portions of the Formal Quotation out to suppliers that can complete certain aspects.  On the other hand, we may just have a brochure that needs to be completed and this Formal Quotation goes to just one type of supplier.  
  • FORMAL QUOTATION to Client.  Once, all bids are received into our office, then we will create a Formal Quotation to the Client.   We would like to send via email.
  • PURCHASE ORDERS (POs). Upon acceptance of the Formal Quotation by Client, then we need to pick and choose the right suppliers to run the JOB.  At this point we will need to produce one or more POs to send out to those chosen suppliers to produce the component.  
  • SUPPLIER INVOICES.  I would like to capture all of the suppliers invoices and tie them to the JOB.  Do I need to create an entity to store the supplier invoices to this JOB?  In essence, I would like to scan and track all of the hard copy cost of goods (cogs) and tie them to every JOB.
  • INVOICING.   This is pretty self explanatory.   After a JOB is completed by the vendors, we will invoice the JOB to the client.
  • CONSOLIDATED DETAILED INVOICES.  Just like above, we may need to Invoice a client once a month for all of the JOBS we did over the past 30 days.

I hope I spelled this out clearly.

With this said, I am asking for input on how to create the most efficient Entities/Table Occurences within the FM Relationship Diagram to have this work successfully.   I have read the Basics and part of the Advanced FM Training Series, but I still cannot get my head around the scenario I listed above.  I think I am making this more difficult than it appears within my industry.   Any insight would be greatly appreciated.

JFC70

 

 

 

Share this post


Link to post
Share on other sites

You've spelled it out clearly, but the devil is in the details, and there are going to be massive numbers of details in this project. Maybe you could take one piece of it and ask a more specific question.

  • Like 1

Share this post


Link to post
Share on other sites

Fitch:

Thanks for the reply.   I know this will have a LOT of details / moving parts associated with it as there are many entities and table occurences that will have to be created, etc.   However, to start the data modeling, I am wondering the best approach for calling out the appropriate Entities for this solution and maintaining a consistent JOB # throughout.  

Share this post


Link to post
Share on other sites

However, to start the data modeling, I am wondering the best approach for calling out the appropriate Entities for this solution ...

To start the data modeling, I would suggest you construct yourself an ERD. Note also that some of your entities seem to be related in a one-to-one relationship and could possibly be united (e.g. Jobs and Quotations To Client).

Keep in mind, though, that an ERD is not the same thing as Filemaker's relationships graph. There are are many possible ways to implement an ERD as an RG, and a lot of room for personal preferences - see: http://fmforums.com/forums/topic/67070-anchor-buoy-graph-model-circular-relationships-many-to-many-relationships-bidirectional-portals/

 

... and maintaining a consistent JOB # throughout.  

Any entity related to the Jobs table, whether directly or indirectly, can reference the related job's unique identifier through the relationship. That's something that's built-in into the application and there's nothing you need to do except make sure you have the right relationships in place.

 

  • Like 1

Share this post


Link to post
Share on other sites

Thanks for the piece on Anchor Bouy and the definitions between an ERD and Filemaker's graph.  As a novice here, I crossed the terminology in my initial post.   

I am developing an RG.  With that said, based on my scenario in my first submission and your expertise, what would make the most sense to be the "Anchors" within the TO Group?   This is how I see the initial development:

Anchors:

  • CLIENT COMPANY
  • SUPPLIER (I am not running Client and Supplier as one table since I have so many different fields for each)
  • JOB
  • SPECIFICATIONS (I have this as its own table since each job will utilize certain specs from here - i.e. Brochure specs vs apparel specs, they are totally different)
  • PRODUCT (for Apparel)
  • QUOTATIONS (use this for both supplier and client)
  • PURCHASE ORDER
  • INVOICING

What are your thoughts?

As it pertains to Job Number Updates, PO segmentation to given suppliers, and Consolidated Invoices, I think that is something to look at from a scripting perspective, am I correct?

Thanks

 

Share this post


Link to post
Share on other sites

I understand that this will be a BIG undertaking and will require a lot of time on my part.  However, I do not have the capital at this time.   If I did, I would have jumped on a FM certified solution partner to address this yesterday!

In the meantime, any feedback would be graciously appreciated.  

I sure you understand as well.

Thanks

JFC70

Share this post


Link to post
Share on other sites

Well; perhaps we do understand - this may not be the place to get that result. Buy the completed product.

Note that one of the suggestions you have received is a free product - FMStaringPoint.

You're asking people to donate their time to develop a complex product for free for you; and deal with the substantial added workload of not only developing the product but explaining the development process and explaining FileMaker, etc. 

It looks like you are standing by an empty lot with a shovel and telling passersby that you want to build a hotel but you don't know how and you've never built one and by the way you don't have any money.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By 360Works
      360Works Portfolio Bundle: Build a complete solution in FileMaker at a fraction of the cost.
           
      Save Money
      The 360Works Portfolio Bundle offers the best FileMaker plug-in deal around. You receive all of the 360Works plug-ins and tools for FileMaker at 75% off the cost of the entire product suite from 360Works, saving you a great deal of money in the long run.    
       
      Save Time
      Additionally, it saves you time by providing one, single license key to register all plug-ins and add-ons in your FileMaker solution. You even save time with updates! The Portfolio Bundle is on our Maintenance model for licensing, this means that your purchase includes 12 months of maintenance, which gives you all new products and upgrades released up to a year after your Portfolio Bundle purchase. At the end of 12 months, you may choose to renew your maintenance subscription for each additional year, for a cost of $695 (or auto-renew at $625.50).
       
       
      The Portfolio Bundle includes over 18 FileMaker products, some of which are exclusive to Portfolio Bundle license holders only. Here is a friendly reminder about all that it has to offer:
       
                   
      Download products and learn more today at 360works.com/portfolio.
       
           
      360Works Support
      Our 360Works Support Team is dedicated to your successful use of 360Works plug-ins and add-ons. If you run into issues we offer 2 free hours of technical support Monday - Friday from 10:00 a.m. - 6:00 p.m. (ET). Most questions can be answered at our documentation/help page and requests for support team assistance can be sent to support@360works.com
         
           
      Stay updated with 360Works
                       
       
       
           
    • By Stickybeak
      I have a pretty simple document generation and management solution to which I would like to add an email capability.  I would like to be able to have my email attachments dealt with FileMaker so as to be given a unique name and allow me to save them to my HD all in Macintosh.
      Any body interested in taking on this task for me?
      I will buy whichever plugin will do the best job or suits the successful consultant.
      Management_Clone.fmp12
    • By 360Works
      360Works at DevCon 2018: Discount Extended!
      We had a great time at the 2018 FileMaker Developer Conference in Grapevine, Texas. Nothing gets the 360Works team more excited than demonstrating our products and meeting other FileMaker Developers! We are pleased to announce that we will be extending our DevCon discount until Friday, August 17, 2018. Enter code DEVCON18 at checkout and receive 15% off of your purchase!

      Use the product table below to learn more about your favorite products and take advantage of the discount.
      At DevCon this year, Joe Martin demonstrated the brand new 360Deploy 2 for completely automating deployment of development changes to a production server and Jesse Barnum demonstrated a new version of MirrorSync, version 5, which has more convenience for end users and over 40 improvements to reliability, speed, and stability.

      At our booth, the MirrorSync Setup Station allowed many DevCon attendees to return home with a fully functional sync setup and Richard Carlton Consulting gave out free FileMaker 17 training books at our booth this year!
      Click on any of our product links below to learn more or use the discount:
      Product Name Product Description 360Deploy Automated, simple deployment of new database versions Charts Easy-to-use charting for FileMaker CloudMail Send and track email marketing campaigns Email Send and receive text and HTML emails in FileMaker DocuBin Document and digital asset management FTPeek FTP upload and download from your FileMaker database JDBC SQL access with JDBC Compatibility MirrorSync Sync FileMaker Go and Pro on a device without network connection, sync FileMaker Servers, and sync FileMaker with SQL databases Plastic Payment processing plugin RemoteScripter Trigger scripts on FileMaker Pro from the web or IWP SafetyNet FileMaker Server offsite backup Scribe Read and write PDF, Word, and Excel files ScriptMaster Adds many new functions & create custom plug-ins SuperContainer A better container field Web Assistant Access internet resources, URLs, and fill out web forms Web Services Manager Publish FileMaker scripts as XML Web Services Zulu FileMaker calendar sync Stay updated with 360Works
       
    • By Rabernethy
      Hello
      I have inherited a database in Filemaker 12 on Windows, but I am completely new to databases. I have a script which generates and saves a pdf for each person using fields from their record. This works well.
      I then have a second script which is meant to email each of these pdfs to the corresponding person. I have included screen shots of the script and the "send mail" options.
      When I tested it by putting only my own email address in the To: box, it sent me hundreds of emails each with a different name in the text, and the correct corresponding pdf attached, as expected.
      Very embarrassingly, when I tried to send the pdfs out, it sent an email to each person with the right name in the body of the text, but all with the same email attachment (for the first person in the list)! What did I do wrong?
      Thank you in advance for any advice you can offer.


    • By Christi Palme
      I'm working on getting QuickBooks' Item Sales Tax groups to pull into filemaker along with it's child records of which itemSalesTaxes belong to the group but I'm so far not having any luck getting my script to work.  In addition, I'm wondering if anyone knows the quickbooks rules regarding assigning the sales tax rate to and invoice - the attribute is named as if it's looking for an itemsalestax name or list id rather than a group name or list id and so far, my attempts to just send it a group name in the place of an item tax name has not worked.  Has anyone done this who could send a sample script?
      best regards,
      Christi
×
×
  • Create New...

Important Information

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