OG School

After watching the video, I was able to add a basic solution to my existing database pretty quickly, as long as the users in on a FileMaker client. I was hoping to be able to have a read-only calendar of events that anyone in our school could look at via IWP, but no joy. There's most likely a way to get this FullCalendar solution to work via IWP, but I just don't have the knowledge or chops to pull it off.

Thanks, Comment. I don't suppose I can pay you to help, hmmm? -Dave

HELP! I DO NOT KNOW HOW TO ALLOCATE PAYMENT PORTIONS TO DIFFERENT AREAS OF REVENUE STREAMS! At our K-8 school, we've got an FMP DB that's been in production for a few years. It has always been a single table DB, and was used primarily as an online registration tool for parents to check over the info we have on file for them in our Student Information System (PowerSchool by Pearson). The reason I used FMP is because it was much easier to set up than to try to make PowerSchool do what we needed it to do. Last year I was asked to track student fees in the DB. No biggie. About 1/2 way through the school year, I was asked to add a payment component. I see what a bad idea retrofitting a DB is, but my hands were kind of tied. The DB now has a 2nd table for payments, related to the student table by student_number. Again, this wasn't hard. The parents register, they select their optional items (milk, band, PE uniforms). Their BALANCE is calculated in the student table: STUDENTS TABLE Student_Number (hook) FeeSummary = OverdueFees + RegistrationFee + BandFee + MilkFee + PeUniformFee When the parents make a payment, it gets recorded into the payment table PAYMENT TABLE Student_Number (hook) Date Amount Back in the students table, the Balance is the FeeSummary - PaymentSummary. The payment summary is the sum of any payments that have been made. STUDENTS TABLE BALANCE = FeeSummary - PaymentSummary So far this has all been easy. Now they've asked me to figure out how to allocate payments to go into different areas of revenue! I am at a loss! When a payment is recorded, they want to know how much of the payment is supposed to go to each area that they need to account for. So let's say a payment of $100 has been made. When they get that payment, they now need to know that $25 of it goes into "overdue fees," $30 of it goes into BandFee, $7 to PEUniform fees, etc. Since parents may make payments several times throughout the year until they reach a zero balance, the bosses have indicated that all of a payment should go into "overdue fees" until the overdue fee balance is zero, then money should go into RegistrationFees until that balance is zero, then any money left over should go into MilkFee until THAT balance is zero, etc. I do not think retrofitting this DB is going to work out very well, so I may have to design one rom scratch. Can anyone clue me in on how I might accomplish this task? I'll try to give a synopsis again. • parents register. They come up with an amount they owe based on overdue fees, registration fees and optional items • parents make a payment • the payment must "fill the buckets" of each area that we charge for until each bucket is full • if the "buckets" have not been filled, their balance will reflect that, and future payments will need to pick up where the last payment left off. I greatly appreciate any help! -Dave

I appreciate your help. My gig finds me doing everything from setting up sound systems to searching email archives and everything in between. This generally means that my skill set is really wide, but really narrow, too. It's clear that I am trying to make a leap into waters that are too deep. Let me learn to swim a little, study the basics, and I will reply at that time. BTW, are you open to consultation/one on one training? I am getting our school to rely on FMP more and more, and I will have funds available. Obviously this project is not school related, but I took it on thinking that if I could get THIS to work, I should be able to handle pretty much any other task we'd need at the school. Thanks, Dave

Yeah, sorry about that. The client has a big "PURCHASE ORDER" across his invoices. Dunno why, but I wound up calling them the same thing. I've fixed that, so I now have these tables: Invoices Customers Suppliers Products Inventory LineItems I totally get you...this is infinitely better. One problem, though, is that I don't see how the example DB you clued me into works with regards to creating a new line item. In the demo, the user can select a ProductID (which is a pull down menu from the LineItems table), and once a productID is selected, a new record is created in the LineItem table. Back in the invoice table, the user is able to select another ProductID in the next line of the portal. Again, only when the ProductID is selected does the LineItems table create another record. When I try this in my own DB, I can never select a ProductID for the second or third line of the portal. Can you tell me how it is that the demo allows us to select a productID from the LineItems portal BEFORE the record even exits? In any event, my temporary workaround is to have a script that creates a new invoice, creates a new InvoiceNumber based on a calculation that the client wants (a combination of date and some other data), then creates 12 new records in the LineItems table, inserting the InvoiceNumber value into each one of them. I picked 12 because I can squeeze 12 line items into the invoice layout. The demo is cooler since there are only as many line items as you need. So now I need to contemplate getting the custom pricing calculation to work (and a lot of other things that broke by getting rid of the numbered fields to make way for LineItems. Any thoughts on the original question? Thank you!!! -Dave

Hello, I've been using FMP since 1999, although in a very limited way (single table databases only). I've enjoyed just jumping in and trying to make things work, and have been fine with the fact that my solutions are not elegant, but they work. I'm up against it now, though, as I've been tasked with the following situation: A business has about 400 products • Many can come in a variety of colors. • some items can be sold as a unit or sold by the foot or both • each product can be manufactured by up to 4 different suppliers • they pay each supplier a different amount, and therefore 1 part number can cost them 4 different amounts • they give their customers individualized prices; God forbid it should be something easy like a percentage off a general price. No, each customer gets a special price on each individual part number that they are apt to buy. This may be 4 or 5 parts out of the 400 available, but each regular customer gets a special price on at least a few items • they need to keep track of how much each unit (or each foot) costs them, compare it with the customer price, and show what the profit would be on that item. They can then change the supplier to see the difference in profit (and what they have in stock) • they need make purchase orders and release forms and tie it into an inventory • the inventory can be in up to 5 places (their own warehouse and the other 4 suppliers warehouses) I feel like I have a significant amount of this solved, even though this is my first attempt at a relational database. The custom pricing for each customer is killing me, though. I've set the DB up like this (I am designing it in FMP 9 on a Mac): Purchase Orders table - most of the interfacing takes place here Customer table - related to invoice table by customer ID Inventory table - related to invoice table by a combination of part number, supplier ID and inventory location Products sold table - related to Inventory table by part number. This table is a list of all the products sold along with general public pricing by unit & by foot. To handle color variants, I gave each one a separate part number (101-blk, 101-blu, 101-r, 101-y, etc). Supplier table - related to Inventory table by supplier ID I've attached a pic of the relationships. I have it so that when the user creates a new invoice record, they select the customer via a value list. All pertinent info is put into the invoice. The user selects the 1st part number ("item 1 part number), and the description of the item appears. When they put a quantity into the units field, item 1 price should appear. If they put a quantity into the per foot field, item 1 foot price appears. Out of printing range to the right, the user can choose a different supplier to check their inventory levels and profit per items. So far, no problem. If you saw it, you may cringe, but the DB is working. Here's where I crash and burn. I don't know how to say something like: "if item 1 part number is 101-blk, then make item 1 price whatever we indicate this customer can have on part 101. We're not going to type in special prices for color variants, so whether the part they want is 101-blk, 101-blu, 101-r or 101-y, make item 1 price the customized price. Oh, and if we DON'T have a special price for this customer on this particular item number, make item 1 price whatever the general pricing is from the Products Sold table." If he just gave his customers 3% off of everything, this would be easy, but having special prices for different part numbers for different customers has me stumped. Please excuse the rambling. I don't post many places, and I'm never quite sure how much or what to write. Thank you for any help, advice, or clarifying questions. -Dave

Okay, thanks. The console says I am now running 11.0.2.217. I'll see how it goes. Thanks again!

Thank you for the reply. This is FMP Server Advanced. Admin Console version is 11.0.1.95. Apache 2.2 web service is running. I am using IWP. PHP, XML and XSLT publishing are disabled. IWP is working correctly for the most part; I've had over 100 families modify the DB via IWP over the past few weeks...web publishing simply stops now and again. The platform is OS X Server 10.6.4. Thanks again for your consideration.

Hello, Our school has used FMP since at least 1999. I've had to do very little to it over the years because it has been rock solid. I installed a fresh version of FMP server 11 on a Mac Mini Server running OS X 10.6 (Snow Leopard) server. Setup went well even though I had to use DVD drive sharing for the first time (since the Mac Mini servers don't have any optical media readers). The server has been running FMP 11 since mid-August with no problems. Once we opened up the lunch database to the public via web publishing, however, I started to get complaints that the server was "unavailable." Looking at the console showed me that web publishing was not running. A simple click would get it going again, and I would not have another problem...until the following day. Or later in the day. Or whenever. It seems that web publishing is turning off intermittently, and I cannot determine any kind of reason for it. Looking at all the modules for errors in the log viewer, I find the following: _______________ Publishing Engine Error FM Web Publishing - - wpc1 Error: 500 _______________ Publishing Engine Error FM Web Publishing - - wpc1 wpc1 Web Scripting Error: 401, File: (script name) _______________ Publishing Engine Error FM Web Publishing - - wpc1 Web Scripting Error: 101, File: (script name) _______________ Publishing Engine Error [28839:0000][error]ajp_get_reply::jk_ajp_common.c (1592)(wpc1): Tomcat is down or refused connection. No response has been sent to the client (yet). _______________ Publishing Engine Error [28839:0000][error]ajp_service::jk_ajp_common.c (1953):(wpc1): Connecting to Tomcat failed. Tomcat is probably not started or is listening on the wrong port. _______________ These errors are repeated throughout, not a ton, but enough to investigate. I have the DB author looking at the 3 or 4 scripts that are throwing the 401 and 101 errors. With regards to Tomcat, it is running and listening on port 80. I would appreciate any thoughts on where to go from here. I tried to call FMP tech support, but I keep getting a busy signal after I go through the prompts to get to tech support. Ugh. Thank you so much! -Dave Just another school tech guy