Jump to content


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About burdenslifted

  • Rank
  1. Is there a function I could use to track layout access: layout name, when used, by whom? This would be useful to clean up a legacy database that has a lot of old (probably unused) layouts.
  2. What's the best way to deal with data that is really only relevant to specific subgroups of an entity? I'm working on a directory of ORGANIZATIONS and PEOPLE. Organizations all have contact info (address, phone, website) and orgtype (e.g., church, school, charity). But some information is only relevant to certain kinds of organizations (e.g., service times for churches, grade levels for schools). I can see three options (TABLES in caps, fields in lowercase). Option 1: ORGANIZATIONS _kp_organization address phone website orgtype grades (blank for
  3. The royalty rate comes from a field in the SONG table, input by hand. To refine TheTomintor's schema to make it more in line with what I've actually got: PRODUCT ------ _kp_product_id UPC ORDER ------ _kp_order_id order_date PRODUCTLINEITEMS ------ _kf_order_id _kf_product_id SONG ------ _kp_song_id song_title publisher album UPC royalty_rate gOrderDateStartRange (global used to filter portal) gOrderDateEndRange (global used to filter portal) cOrderIDsInDateRange = List(song_ORDER::order_id) Because the publisher and royal
  4. The object of this report is to calculate royalties owed to publishers & artists. The way the database is set up (not my doing), each instance of the song has a different record, so Purple Haze would have two entries with two different song_ids, one with the UPC for Smash Hits and one with the UPC for Are You Experienced. (Nice example, btw.) The report needs to be grouped by publisher, album, and song, reporting the quantity of sales for each song (not the dollar amount). The qty is multiplied by the royalty rate to calculate total royalties due. Every song should be on the report, w
  5. Good to know -- I guess I can't blame the fact that it's not working on that then!
  6. Finally getting around to trying this, and feeling dull-witted. In particular (cOrderIDsInDateRange = order_id) doesn't seem right, because the calc field holds a whole set of order_ids which will never be an equal match for the single order_id in the productlineitems record. How do I get it to see the order_id as a match if contained in the calc field?
  7. You've got the setup just right, and this looks like a good solution. I'll give it a try! Thanks.
  8. I can see how that might be useful, but I'm not sure how to apply it here. Which related table would the List field store record ids for? I'm not sure how that would help me relate songs and orders (or productlineitems), since the only match field between the two databases is UPC, found in the Products table. Can you be more specific?
  9. Working in FM 10... I have a database with a standard many-to-many relationship orders--products for CD sales, but also a separate database that lists individual songs. Each song record has a UPC code that corresponds to a CD's UPC code found in products. I need to generate a report on sales by song during a certain time period. How do I set up a filter based on order date for a productlineitems portal on a layout based on songs? Or is there a better way to do this? The data needs to be combined with other sales info, so I'd like to avoid a search method.
  10. True, it would do nothing to solve that problem, but it would solve some other issues I'm tiptoeing around and make me feel better about the vulnerabilities introduced the join table process.
  11. Thanks both of you -- the client doesn't much care about the more flexible reporting options at the moment, so I think I'm gonna avoid the tedious and potentially error-prone join table for now. I am comforted to know that it's a sticky problem, and not something with an obvious solution I couldn't figure out. Maybe I can convince them to rebuild the database at some point in the "right" way.
  12. Thanks -- you've got the problem right and I'm glad to know I'm not crazy to think this is a bear, even though it seems like all the data I need is there. The path you've described is just the one went down before realizing that there was 100,000 records to import. If only 10% are album sales, and each album averages 10 songs, that's a heck of a lot of processing to do on a regular basis. It is server based, but I'm working remotely, so it's not really practical. I'm now working on an alternative solution based on the songs database, with two filtered portals to the imported sales data ta
  13. Okay, that's sort of what I thought, but needed to check it out. Legacy database you know -- thought someone before me might have known what they were doing, but apparently not so much.
  14. I'm trying to produce a report for payment of royalties by a company that sells both digital and physical (CDs) music. Sales can be based on tracks (individual songs) or albums (i.e., collections of songs); royalties are paid out based on sales numbers. The problem is that the report from which the sales information is extracted doesn't directly link the albums with the songs contained on the album. The song database contains a record for each song and relevant info: kp_song_id (a number that uniquely idetifies each song or song part on which the royalties need to be paid) title artis
  15. I am working on an older server-based database, now on FM 10. The database is set up with two separate files, a song file with info on songs and a sales file with info on individual song sales (downloads) that is imported from an online retail outlet. The sales file is 900,000 records and growing. The database was set up to use lookups from the song file to the sales file, rather than importing sales records into a table in the song database, on the the theory that so many records would bog down the database. Which is better, using lookups or keeping that many records in a single database?
  • Create New...

Important Information

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