# pehrlabel

Members

10

## Community Reputation

0 Neutral

• Rank
novice
• Birthday 11/16/1976
1. ## UPC checkdigit formula

hello everyone, if i had a field that contained 11 digits, how would i write a new calculation field that would make this checksum, and create a new 12-digit number with this checksum as the last digit? this is for our UPC codes in our database. right now we are looking up the checksum manually on the internet. we'd rather have filemaker do it. I've found the formula below, but i don't know how to write it in a calculation. Thanks!!! adam "the checksum in UPC-A is standard modulo 10 calculation: 1. Add the values of the digits in positions 1, 3, 5, 7, 9, and 11. 2. Multiply this result by 3. 3. Add the values of the digits in positions 2, 4, 6, 8, and 10. 4. Sum the results of steps 2 and 3. 5. The check character is the smallest number which, when added to the result in step 4, produces a multiple of 10. Read about UPC-A at http://www.mecsw.com/specs/upc_a.html"
2. ## One To Ones Or All In The Same Table?

Okay I've got my subtypes laid out now. I have companies, with a unique ID for each company (serial number), as my primary key. each company record has a field for category, and these categories (retail, distributor, etc) each have a subtype table now, thanks to your previous posts, with specific info for that category. I have a distributor subtype table, with a unique ID for each record. I have a store subtype table, with a unique ID for each record. One store can have several distributors, and one distributor can have several stores. I think I set this relationship correctly with the join table: I have a join table called "distributors stores," with the ID for distributors and the IDs for stores as the two keys in each record. How should I relate companies to their subtype tables? And what if one company has more than one subtype (like a distributor that also has a store)? For example, I want companies, whose box I check "distributors" under the category field, to be linked to a new record with that set of fields in the distributor subtype table. I also want companies, whose box i check "stores" to be linked to the stores subtype table. Should I just create different layouts that show data from the Companies Table, and linked records from the subtype table? When I think about linking the company ID one-to-one with the subtype ID, then I wonder "how can I tell filemaker which subtype table or tables to link to" I thought maybe there is a script that can trigger when i check one of the categories boxes, but this might be way too complicated and unecessary. Thanks if you have any tips!
3. ## Converting Non USA Dates?

It worked exactly perfect. I tested it alone, and now i'm going to bring those calculation fields you made into my database. Luckily the information I get is very consistent. A robot from the distributor sends the information, so humans like me won't be able to make typos. Sorry I take a while to reply after you nice people offer your help. I just go out of town a lot, and i don't take filemaker on the road with me.
4. ## case calculation with weird exceptions?

LaRetta you are a genius. You are going to laugh but you have saved me HOURS of work! HOURS. And now from your syntax I can try it myself if any new exceptions come up. The more of this table I can automate, the more new business I can do without worrying about having to do the accounting by hand, so let me say you are awesome. Oh I didn't know that about wildcards, how one * goes on forever to the right, whereas the # is specific for one character only, thanks. So I just visually compared a few thousand records, and the calculation matched what I had done by hand perfectly. Wow. By the way if I can continue to gush, I was reading some of your other posts about dates in filemaker, and those were really helpful.
5. ## Converting Non USA Dates?

When I import records from a distributor, if the country field is not USA, the days and months are reversed in the date fields. Instead of mm/dd/yyyy I get dd.mm.yyyy I have two sets of date fields for every record I import, a start date and a closing date. And the non USA dates are made with periods instead of slashes. can I set a script or something so that if it says USA in one field, it doesn’t change the two date fields? but if it does NOT say USA in that field, it switches the dd.mm.yyyy to mm/dd/yyyy in both of the date fields for that record? right now i'm just looking them up and doing a batch replace, but i bet there might be a way to do it when I'm importing the records. Thanks! Adam
6. ## Convert FMP datafile into MySQL file

I'm thinking that Filemaker 9 might solve this problem for you. You wouldn't need to convert or export, you could just share. Someone will have to know MySQL that works with you, but FM9 should be able to share with SQL databases just fine. It shares 2 ways, so if someone sets it up for you, (the tables and field types in MySQL, and the relationships in FM9), you might be able to keep editing and updating in filemaker 9 and the changes would go into your MySQL database automatically. They have to be on the same local network though, I don't think this works remotely. I am brand new at this, and I don't even have filemaker 9 yet, so take my advice with a grain of salt. (Smart people feel free to jump in on this topic and correct me : ) If you have to do the MySQL yourself, Navicat works well, and so does SQLEditor on OS X. And OpenOffice's Base program is free, and I think people seem to like it. There are lots of good MySQL books for beginners, but weigh the time it takes to learn MySQL vs. the money it costs to just hire someone to set it up right. Cheers, Adam
7. ## case calculation with weird exceptions?

Thanks Comment! The asterisks are wildcards. And I meant the 8th character, sorry. Because the codes in those fields, sometimes they are letters, sometimes numbers. Digit was the wrong term. thanks again, adam
8. ## One To Ones Or All In The Same Table?

Oh whoops i should have checked the replies to my post within the last 2 months. sure, here's an example: i have one contact who is at a radio station, so there are fields for the radio station's wattage, frequency, request phone #, etc. but then i have another contact who is an editor for a magazine, and that contact needs fields for whether it is national, local, or online, whether they write features or reviews, etc. so some fields are all the same for contacts, and then each type of contact might have 5-20 unique fields just for their type of job. and i have about 20 different types of contacts. so i wasn't sure if i should, for example, have an editors table and a radio stations table, or just have a "people" table with the fields for all the types, and then have a layout for each type, so i will only add fields to the layout for that category of people. i guess both ways would use layouts for each category. one way would have a whole lot of empty fields in one big table, the other way would have one-to-one links from the people table to the specific category table for all of the standard fields (like name, address, phone, email), and then unique fields just for that table that focus on the unique fields of that person's category. thanks, adam
9. ## case calculation with weird exceptions?

help! I import tons of records each month into my filemaker, and I’m assigning them percentages by hand, clicking the radio buttons for hundreds of records a month. i have to stop this madness. I need to make a percentage calculation field whereby: Field A is a text field with numbers and letters. if Field A's 8th digit is 0, then my calc field = .5 if the 8th digit is NOT 0, then my calc field = .85 except for 2 exceptions: 1. if the value in this field is 676347700721, or US4G706007** then this field = .33 2. if the value in this field is 676347799220, or US4G705992**, then this field = 1 Is there a case calculation that can do all this? thank you so much if you can think of something! - adam
10. ## One To Ones Or All In The Same Table?

I have one table of contacts, with all of the same general fields like name, address, etc. There are about 12 different categories of people i work with, and each category of people has unique fields, but never more than 20. Should I create one table of contacts, fill it with all of the fields for the different categories, and then create one layout for each type of contact? meaning one layout per category? So I will have a generic contacts layout, and then add the category-specific fields to each duplicated layout? I know this is not using filemaker to it's full potential, since this way would just be a flat database, but this would work just fine for me, since I will never have more than 10,000 contacts, i don't think it should slow down filemaker too much to have empty category fields in each record. Or should I create just the generic contacts table, make one-to-one links with every field of the duplicate tables for each category, and then add specific category fields to each category table? This way would be easy, too, but I just don't know if it's necessary. Or am I stupid and there is a third way to do this? I've read about ERDs and I'm reading the great "Using Filemaker 8" book, but i'm not sure i've figured it out yet. Any advice would be awesome! Thanks, Adam
×
×
• Create New...