Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
amdiaz

Creating Record via Relation (only if one does not exist)

Recommended Posts

amdiaz

@AHunter3  Posted in a reply to an ancient thread (below)...

Which describes where I'm 'stuck' at right now pretty much down to the detail.

But even this seemingly simple approach described doesn't seem to work for me.

I want to have a separate table for more details on a particular vendor/manufacture/developer, but I'm not concerned about back filling all that stuff right now, so the comment about the trade-off is perfect.

So, I use the existing values to autofill the field.. if the value appears as an autofill option, then I know it's there already. If not, I can press that big plus button to add that to the Vendors table.

2030310073_ScreenShot2020-04-22at13_54_33.png.d355c7fa26a8be7428a2a2ad36a69569.png161685580_ScreenShot2020-04-22at13_58_32.png.62a9edc25f6c09f7f0609911c179f9e6.png

 

..and for a bonus, hide the [   +    ] button if the value already exists.  (Hide If voodoo involved here.. )

 

I created a script:

Set Field [ Vendors::Vendor ; GetField ( Records::Item Vendor ) ]

 

When I press the button I get a blank record in the Records table and nothing in the Vendors table.

 

 

 

On 8/24/2006 at 7:47 AM, AHunter3 said:

You have a field "Name" in Products. It has a value list which is drawn from the "Name" field in Manufacturers. You manually type in a new/different value in Name in Products. That name hasn't been added to the Manufacturers table, only to the Products table. The value list draws from Manufacturers, remember?

Could you use the "allow creation of records via relationship" feature to auto-populate the Manufacturers table with a new entry, thereby adding that new name to the value list? Yes (but not without tradeoffs):

There needs to be some field over in Manufacturers, aside from Name. Let's say it's Creation Date. You create this simple script:

Set Field [Manufacturers::Creation Date, Get(CurrentDate)]

...and attach it to an "Add" button next to the Name field in Products. Then if the manufacturer's name isn't in the value list, you type it in manually, click the "Add" button, and it has been added to the Manufacturer's table (and thereby to the value list)

The tradeoff: I assume the Manufacturer's table probably contains a lot of fields. Address, Telephone, web site, FAX, stock ticker code, category, etc etc.... auto-adding values in the above-described fashion means you end up with new records that only have names. So you have to come along behind and find them and add the missing info.

You might prefer that, instead of adding names on-the-fly like that, you have your "Add" button navigate to the Manufacturer's data entry screen; you create a new record there, put in all the data, then click a button that says "Return to Products", go back to your name field, select the name (which is now in the value list), and continue about your business.

 

 

Screen Shot 2020-04-22 at 13.38.15.png

Screen Shot 2020-04-22 at 13.32.44.png

Screen Shot 2020-04-22 at 13.53.01.png

Share this post


Link to post
Share on other sites
Steve Martino

Get Field is incorrect.  I really don’t understand what you are trying to do.

Share this post


Link to post
Share on other sites
amdiaz

Two tables:

I want the vendor name only to show up, like a customer record associated with an invoice.

... but with the vendor field getting that value from existing records in the vendor table.

I can do that. Use a valuelist that gets values from Vendors::Vendor Title for entering data into Records::Item Vendor.

 

1164457010_ScreenShot2020-04-23at07_44_15.png.500e2d9a0a3d7a9b6cb7f11a08709a55.png

 

(and yes, there will be all those other empty fields .. that's for a different time/function but I'd like to populate the table as I go )

If I just use  Vendors::Vendor Title on the Records Layout then I get duplicates.

But if while I'm typing it in, I see that the value I'm entering isn't already there, let me press a button on the Records Layout that takes the value and creates a new record in Vendors:: ....

Share this post


Link to post
Share on other sites
AHunter3

Are you saying you want to create a new related record in VENDORS if there isn't already a related record in VENDORS?

(In other words that if there isn't one there already you want to create one)?

 

Or are you saying you want to create a new related record in VENDORS if there isn't already a related records over in the RECORDS table?  (That's a totally different kind of "if")

 

 

  • Thanks 1

Share this post


Link to post
Share on other sites
amdiaz

I would like to keep an ongoing list of VENDORS, free of duplicate entries.

But in RECORDS I'm only putting the name there. (through relation), so that I can -later- go and get information on whatever VENDOR.

I would like to add a new record to VENDORS if there's not already one there, and then that Vendor Name goes in RECORDS.

So if I'm using the existing entries from VENDORS::Vendor Title as the Value List I'll know if what I put in the box exists in VENDORS. But of course, if I have VENDORS::Vendor Title on the Layout that is using RECORDS It's just going to create a related record each time.

Quote

"The tradeoff: I assume the Manufacturer's table probably contains a lot of fields. Address, Telephone, web site, FAX, stock ticker code, category, etc etc.... auto-adding values in the above-described fashion means you end up with new records that only have names. So you have to come along behind and find them and add the missing info."

.. and what you're describing seems pretty standard in a POS environment. Enter enough to get an invoice processed, back fill later if you care.

So what I figured I'd do is something along the lines of using FilterValues against the Value List of VENDORS::Vendor Title to determine if whatever is typed into that field already exists. If it does, carry on. If it does not, make a button visible that would say "Add As New Vendor.." and then when it's clicked it disappears because it's been added.

Then you've got the ability to mention something in a one-off occurrence as a side effect also.

Share this post


Link to post
Share on other sites
AHunter3
Quote

I would like to keep an ongoing list of VENDORS, free of duplicate entries.

But in RECORDS I'm only putting the name there. (through relation), so that I can -later- go and get information on whatever VENDOR.

I would like to add a new record to VENDORS if there's not already one there, and then that Vendor Name goes in RECORDS.

OK. 

No, I don't think you're on the right track for doing what you say you want to do.

First we do the adding of the new vendor to VENDORS if there's not already one there.  From the context you're sitting in, you have a vendor in mind — that one that either IS or IT NOT already in the VENDORS table.  With me so far?  You have it either by name or by something like Vendor ID, I don't know which, but presumably you do.  For this example let's assume the vendor is question is named "Acme Inc" and a local field here on the record you're currently on has that information in a field.  If that isn't quite how it is, you have picked it somehow or it's in some third table that's related to this record -- you "have" the vendor in front of you somehow.

 

a) If you don't have the Vendor in a local field, create a local table calculation field and define it in such a way that it reconciles as the Vendor as you're able to perceive and know it in this context.  I can walk you through that if you think you need to do this but don't understand what I mean.

Either way, you now have Vendor (by name or by Vendor ID or whatever) here in a field in the table that your current layout shows records from.

b) If you don't already have one, make a relationship to the VENDORS table using that local field and matching it to the corresponding field in the Vendors table.

EXAMPLES:

by Name -

YourLocalTable::Vendor Name = VENDORS::Vendor Name

by Vendor ID

YourLocalTable::SelectedVendorID = VENDORS::Vendor ID

c) In that relationship, check the checkbox that allows record creation on the VENDORS side

 

Now all you have to do to add a new vendor if and only if it isn't already in there is to set a field or to to values they would already have or to values we don't need to care about.  If there's an existing record, we'll be modifying it meaninglessly, and if there's not, a new one will spring into existence to accommodate those actions.

FOR the By Name Example:

Set Field [Vendors::Any_IrrelevantDateField; Get(CurrentDate)]

 

FOR the ByVendor ID Example:

Set Field [Vendors::Vendor Name; YourLocalTable::Vendor's Name]

 

 

Now in some table called RECORDS -- assuming it's other than the table that your layout is showing records from — you also want to create a new record with that Vendor name fo rreasons you described.  

You shoudl probably pursue the exact same strategy.  A relationship to RECORDS through which you write something out which results in a new record or else just refreshes some fields in an existing record.

 

Whereas if you're already IN the RECORDS table -- that that's the table your current layout shows records from -- you've either already made a rec here in RECORDS or else you do it through the same process except with a self-join relationship (another thing I can help you with as need be).

 

 

 

 

 

 

 

 

 

 

Share this post


Link to post
Share on other sites
amdiaz

After a bit of experimenting and a little more watching FMP videos, this makes sense .. and I got it working.

Thanks!

  • 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.

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.




×
×
  • Create New...

Important Information

Terms of Use