What you should be doing is have a brands table, a product types table and a brands product types join (associative entity) table because the data relationship between brands and product types is many to many.
Having said that you can probably use PatternCount() for what you asked to do.
FYI this is just one of the cludgy workarounds that you may have to do if your schema is not right.
If I use the schema you recommend (which I like), is PatternCount() still the right approach?
see how this functions and then figure out how to import the data and construct your records appropairtely.
Without knowing alll the details of what data your trying to import i cant make any specific reccomendations
testing.fmp12.zip 65.8 K
Hi, thank you for your help.
Your file isn't exactly the problem I'm trying to solve, but now I think we are close.
Here is a sample file of what I'm trying to achieve in very simple terms. I'm trying to recognize brand names in product titles from a separate brand name field, ~and then populate another field with that discreet brand name.~ A binary output won't do.
I couldn't figure out how to uploade, but here is a link to a clone with dummy data. any ideas?
I'd still really appreciate some help on this, if possible.
Your example is too simple, since all resultBrand can be calculated with
LeftWords ( resultTitle ; 1 )
and if you need test it is in the brandList, use relation.
Real data may be more complex.
EDIT: Sorry, I just re-read your OP and I see that you only want to extract data into another field, not necessarily link tables. The answer has already been given, if the data is consistently entered as <Brand> <Product Type>.
Create a calculated field in your Product Types table which replaces spaces with carriage returns, e.g.
ProductType_CR = Substitute ( ProductType ; " " ; "¶" )
Then create a relationship from ProductType_CR to the Brand Name field in your Brands table.
As mentioned by others, this isn't a replacement for a good schema but will link your Product Types to Brands.
Thank you so much. I'm not sure if you looked at my file from dropbox, but I don't have a producttype field, and I don't want one.
How would I do it with just two tables? 1. Results that have a brand name somewhere (not necessarily the left) in the field. 2. a table of brands to pull from
Yes, the real data is more complex, with the brand name possibly appearing anywhere in the field.
Ah. In that case, use my instructions above. Sorry, I used ProductType_CR as the name of the calculated primary field in the products table but it looks like ProductTitle_CR would be more appropriate.
In any case, FileMaker will link records using a carriage return-delimited list of values. For example, as long as Sony appears on its own line somewhere in the ProductTitle_CR field it will match up with Sony in the match field in your Brand table.
I hope this helps.
My thought was there may be some bland like "Procter & Gamble", and if there is another bland "Procter" how can I do with it.