Jump to content

Tim Falconer

  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About Tim Falconer

  • Rank
  1. Hi folks, This question seems too simple, and I can figure out a number of ways to do it, but I want to know what's <good> and <right>... I've got a main "orders" table, that is a typical orders-with-line-items kinda thing. Happens to be called "Job". I've got an "address book" table, also very typical. Happens to be called "Contacts". I have a join table between these, because a Job can have many Contacts - "sold to", "ship to" "carrier", etc. And obviously a Contact can have many jobs, and many roles - sometimes "sold to" is the same as "ship to", sometimes it's not. User Interface: Job records are initially created in the Quote layout: a button called "New Quote" creates a new Primary Key for a new Job. Then, there's a button called "look up contact" for the quote. This will always create a relationship for the "sold to" contact. It takes you to the "Contact Details" layout (straight out of the Starter Solution). There's a button on the Contact Details layout, "Save this contact to quote". ... Somehow, we have to end up with a record in the Contacts Join table that has: the foreign key for the Job the foreign key for the Contact the "contact type" field with the contents "ship to". It seems that the Job and Contact Type are, together, a composite key with respect to the Job, which is to say, they must, as a composite, be unique. That is, if one has a job open and seeks to change the contact on the quote, it should over-write the Contacts foreign key in the join table record for this Job's quote, and not create a new record. Maybe this is a totally hare-brained scheme to begin with? Do I even want a Contacts Join table, or do I just want a Foreign Key field in Jobs for each of the distinct types of contacts, i.e.: KF_SOLDTO KF_SHIPTO KF_CARRIER KF_AP KF_AR ...etc? It's this possible proliferation of KF's in the JOB table that gave me pause, but maybe that's really a good way to do it...? I've been going back and forth. On the other hand, even if that's the case, I really want to know how to do this in general: I have two tables that need to be joined up by a join table, how do you create the record with both KF's? Do you write variables with the keys, create a join table record, and paste the two KF's in there? How do you know if that join table record already exists? You don't want to create duplicates, do you? (If this sounds similar to what I was asking a couple of weeks ago, it is... but it's only similar: this is a different problem... I tried to apply the solution to the other one, and had to come back to ask here... after googling for hours...) Thanks again, tzf
  2. Good to know, Vaughan, would you care to elaborate or point me at some relevant article on alternative methods for structuring TO's and their relationships in FMP? I'm very interested in this topic.
  3. Ha! So it is, Lee! I noted the date but not the year. So I guess I was no help at all... c'est la vie!
  4. In each line in the portal, there is an entry field for "Crate". It's a drop-down list, consisting of the numbers 1 through 10. The "Add to Crate" script reads the selected value... if the shipping manager has not previously selected "4" for that Shipping Order, then Crate 4 is a new crate.
  5. Thank you all so much. I've "+1'd" each of your comments but that does not come near to expressing my gratitude. And I've been lurking here for a few months, and have already learned much from each of you by reading other threads. Thanks so much! tzf
  6. Thanks so much, both of you. This is very helpful. I don't know whether that statement is meant to be ironic, but if I could ask, without sounding like a total idiot, is it always a not(good) idea, or only sometimes? I guess I really don't care though - by which I mean: my goal is not to learn every intricacy at this point, but to learn the basic best practices at my level. It appears that I've found the best practices for a newbie here, but part of being a newbie is that you may not recognise the good stuff when you see it (or write it). So, thank you both, again, for your help. Sincerely, tzf
  7. Thank you again for helping. The second ERD is correct, and all of those relationships are shown correctly. Every time the shipping manager presses the "add to crate" button, it creates a record in ShippedItems. But it should only create a new record in Crates when needed. thanks, tzf (off-topic, what software do you use to create these neat ERD's?)
  8. Thank you, Comment. The conditions are restated further down, "If Parent Field A..." etc. In set terms, call "parent field A = child field A" set X, and call "parent field B = child field B" set Y, The set that does not cause a new record is (X intersect Y), and the set that causes a new record is (not(X intersect Y)). But maybe that's not any clearer at all! In the real world, it's actually pretty simple: We're packing parts into crates. Parent Field A is the Shipping Order (i.e. the KP, a get(UUID)-generated string) from which the parts are being selected. The parts are on line items, and can be selected by (the Job Order's) Line Item (one part number per line item) and Quantity to pack in a given crate. Parent Field B is the Crate Number (the shipping manager picks Crate 1 through 10 on any given day... starting with crate 1, they rarely if ever pack 10 crates in a day). The shipping manager can select quantities of parts for crates in whatever order makes sense for packing the crates as he goes down the list of parts for the Job Order. I want to create separate record for each crate, which itself will get a unique Crate ID (and a unique QR Code). So for Shipping Order 4321, if Crates 1,2, and 3 have previously been created, and crate 4 has not, when the shipping manager selects parts for crates 1,2,3 and 4, a new record will be created for Crate 4 only. The other parts will be "packed" into the existing Crates 1, 2, and 3, which is to say, the Shipping Order Line Items for Crates 1, 2, and 3 will have their Foreign Key field for Crate filled with the Primary Key for the Crate in which the Line Item is packed. Finally, after all the parts have been assigned to Crates, packing lists are created, one per Crate, which show the Shipping Order Line Items in each crate, along with various dimensional information (mainly Crate Net Weight), while the Shipping Order itself shows all of the line items in the Shipping Order, (once again with dimensional information such as Total Net Weight) and all of the crates to which the line items have been assigned, from which a Bill of Lading can be created, when necessary. (and Child Field A will be the Crate's KF_Ship Order, and Child Field B will be the Crate's "Crate Number" for the day's ordinal crate number 1,2, 3, etc.) I hope that's clear. Lots more detail in the actual solution of course. thanks tzf
  9. I'm somewhat of a novice myself but maybe I can help. Are you familiar with One-To-Many Relationships, Many-to-Many Relationships, and Join Tables? If not, the FileMaker Pro Training Series, Module 3, is a great place to start. Googling "Join Tables" brings up three great articles at the top, including one by Dwayne Wright, who's taught me a ton of things via his blog. In fact, in his article on Join Tables one example he lists is: "Phone Number, linked by clients, leads and vendors". That sounds a lot like your question. Please let me know whether this is helpful to you, or if you need more specifics. thanks tzf
  10. I'm on a roll tonight... I hope I'm not overstaying my welcome here , and also that this question is not a "tl;dr". A friend had a glance at one of my scripts and expressed concern at the number of variables I was using in a script to write values into my "Shipping Line Items" record (maybe 10 variables, all local). At some point, she had said "Lookups are useful where they are useful, but using variables is OK where it's appropriate." It appears that maybe I took some undue liberties with this... In this case, the source data is in one of two portals at the top of the layout. The user can "Add Line Item" to the Shipping Line Items table from either portal. Depending on whether the user adds a line to Shipping Line Items from the first portal or the second portal, the data (including a few "entry fields") comes from the portal line in that portal. The new record contains 8 or 10 fields of data because it's a record of what was scheduled for that shipment. Since the data for each field can come from either of two other tables, a simple lookup does not make sense. (Of course there are ways around that, but one hopes to find the most straight-forward, maintainable, and simple solution) This solution I'm working on does a lot of this: it creates records whereby we track transactions, from Quotes to Jobs, from Jobs to the Production Floor, from Inventory to the Production Floor, and from the Production Floor to Shipping. Is there anything you can clarify about when it's appropriate to use a bunch of variables to create such transaction records and when it's not? Is there a rule of thumb: "use lookups first where appropriate"? Or is there some other process of which I don't yet have the concept? Am I thinking of the whole concept of creating transaction records wrong, somehow? I am under the impression that a complete record of a transaction should contain all of the data which pertains to it, not relate to data in a different table. For example, here, my Shipping Line Item contains particulars about the parts in the Shipping Line Item, even though the data which is not unique to the Shipping Line Items is also stored in one of three other tables: Job Order, Job Line Items, and Part Numbers. Likewise, the Shipping Order (which is the parent of the Shipping Line Items) then creates one or more Packing Lists, and optionally, a Bill Of Lading. All of the data for these is in the Shipping Order, but I'm copying it into the Packing Lists (and where needed, some of it into a Bill of Lading). I'm doing this because I think that it's the right thing to do, to have "real records" of what happened. Those records are then locked. If the data is in different tables, it makes it harder to lock it... right? Not having done this before, I don't know if it's really the right thing to do or some total novice bonehead mistake. Thanks in advance for putting up with me :)
  11. John, Thank you kindly for your response- quite informative. Sorry I didn't respond earlier, been working on other things... Also, thanks for the pointer on the EULA! Thanks, tzf
  12. Hi there again! Here's a little puzzle from a relative novice (me): I have a parent table and a child table in which I want to create new child records when and ONLY when either <one> OR <both> of TWO fields is unique. Multi-Criteria relationships don't do that... or is there something I'm not seeing? I've experimented, with the multi-criteria being "Parent Field A=Child Field A" AND "Parent Field B=Child Field B", via a portal, when the first field in the child record is made non-empty and exited, a new record is created, regardless of whether the child field was a match field, and all empty match fields are filled with the data from the matched data in the current parent record. But you knew that... That does not work for me, because I only want to create a record when either or both of the fields contains a unique value. ...In my case, I only want to create child records where, given match fields A and B in both parent and child tables: If Parent Field A does not = Child Field A OR Parent Field B does not = Child Field B, OR both, create a record; If Parent Field A=Child Field A AND Parent Field B=Child Field B, (for any Child record) do NOT create a record. How would you do this? Script "Find A=A AND B=B", then IF found set=empty, create the record and paste in the match field data for A and B? If there's a way to do this by setting up the relationship differently, I want to know about it! (and I really hope there's some really simple solution I've just overlooked...) (I just experimented with A not equals A and B not equals B in the relationship graph... no blank field in the portal!) thanks, tzf
  13. Hi there! This may seem like a totally obvious question, but I've read the internet, and it says that "Allow Creation..." is for use, basically, only with portals. But what if the related table, where you want to make a new record, is not one with any fields on the layout? Can you use the relationship to create new records via a script, or is that just another one of those "don't try that, it's stupid" kinda things? I'm thinking it <is>. Is there no way to use the "Allow Creation..." trick to create records via a script? I mean, other than having the script "Set Field" (or "Set Field By Name") in a portal? ( I know you can do it without a portal, and that it's, basically, pointless. If you're going to have those fields on the layout, go ahead and put 'em in a portal. No problem. Works. ) So let's say I want to be able to create a new, related record via a script. The two choices are: a) make the portal you need and use it to create records by using "Set Field", or use "New Record/Request" to create the record, and then use variables to paste in the match field data. Is that correct? Thanks so much!
  • Create New...

Important Information

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