Jump to content


  • Posts

  • Joined

  • Last visited

Profile Information

  • Gender

j4jason83's Achievements


Apprentice (3/14)

  • First Post
  • Collaborator
  • Conversation Starter
  • Week One Done
  • One Month Later

Recent Badges



  1. I worked on this last night and finally made some progress. I am now storing everything in my LineItems table. I added a few fields to deal with the time records. I also added LineItem_Type so I can filter for specific types of records later on. I created 2 portals on my Invoice Layout that show my LineItems table. One shows the fields for Time, the other shows fields for Materials. Now what I need help with is creating my Invoice Layout so I can print it out. I want to have all the "TIME" records lumped together with a subtotal below those records. Then I want the "MATERIAL" records grouped together with another subtotal below for those records. At the bottom of the Invoice, I want a subtotal for all the items being billed. Where I am having issues is that I can't have more than 1 BODY section. So I am not sure how to go about this. If the invoice spans more than 1 sheet, I want it to say "Continued on Page 2" or whatever page is next. Then at the bottom of the last page, I want the grand totals. Do you have any recommends or examples I can look at for implenting this?
  2. What if I link my Timesheets table to my LineItems table as well? Then everything would be in one table. So my structure would look like: Invoice ---< LineItems --- Products --- TimeSheets Then on my LineItems portal I could select either Product or TimeSheet and then have it show the appropriate records in the value list so that I am only presented with the records that apply to a specific customer. If everything is in one table, how would I go about separating the LineItems so that all the Timesheets are grouped total with a subtotal and then the materials are grouped together with another subtotal?
  3. Hello all, I am working on designing a report that I can print out and send to each customer. On this invoice report, I want to display related records from 2 related tables. I had thought about using portals but they don't print well. The first table will display the time sheets entered in for this particular project. The second table will contain any materials used that were used on this project. I am not sure how to go about getting this to work with data from 2 different tables. Some projects might only have records in one table, so I need the other section to disappear when there aren't any matching records. I want to have a subtotal for each section if there are records to be printed. My database structure is: Invoice ---< Timesheets_LineItems Invoice ---< Material_LineItems I thought about using parts, but I can't seem to figure out which parts I would use to accomplish this task. Any ideas would be greatly appreciated.
  4. Can you elaborate on the constant field a little more? I think that knowledge might come in handy in the future as well.
  5. I am working on designing a database where someone can custom build a computer. I have tables for each type of component. I have built some conditional value lists that only show compatible parts. Now what I am trying to accomplish is how to filter out these parts to only show items that are still available. I don't want it to show items that are compatible but discontinued. I have a field that I have a drop down list that I have selected if an item is "Active", "Unavailable", "Discontinued". I am not sure how to have my my value lists filter based on this condition. Any help would be greatly appreciated.
  6. Would I create the global field in the join table or in a different table? And you mentioned about creating the relationship to the child records. Isn't this done when I created the ID_INVOICE relationship?
  7. The unpaid invoices don't need to be there until I after I have selected the account to which the payment belongs. Then the portal would populate with the invoices. Do you have a sample or could you explain a little more how to go about and sort or filter the portal records? As I mentioned before, my join table is empty. Should it be? Or will the records be there once I enter a payment?
  8. I have a TO of my INVOICES table. The relationship diagram I posted above shows that its connected to my join table. So I believe this is already in place. Is my relationship correct? Do I need to change it? This field is in my join table. I called it Payment_Portion. And on my screeenshot of the layout, its the last field under the subheading of AMOUNT. I have added another screenshot of my join table. Hope this helps. Can you explain this a little better? Wouldn't I need the invoice balance to be stored so that the database knows if its unpaid or not paid in full? When I started this project, I started googling payments and such and ran across the Core3CRM application. Unfortunately, if I wasn't already setup with my current database, I would have thought about switching as that handles payments pretty well. I downloaded the demo and I like how they have it configured. Unfortunately, I am not really to shell out lots of money and time in switching to a new database when the one I have is working just prefect. The only thing I don't have is a way to effectively keep track of payments and which invoices have been paid in full. I would like to be able to select the Account, enter the info for the payment and have the portal populate with the unpaid invoices. I am just not sure where to go from what I have. My join table doesn't have any records in it. I am not sure if I have to create those each time I make an invoice and receive money, or if I need to make a script that will create the join records for me. Since I am still learning FileMaker, that is why I have posted on here in the hopes that someone can walk me though and help me setup this payment portion of my database.
  9. My thoughts are that I will go to my PAYMENTS layout, click NEW RECORD, select the ACCOUNT, the portal will populate with outstanding invoices. There is a box in the portal for amount of the payment. I click on the checkbox on the left side. Enter an amount on the right side and it will subtract that amount from the invoice total. On the layout that I posted, there is an AMOUNT field. I want to be able to enter in the total amount received (ex. $1000) and have it pull all invoices that have a balance. I can then enter in to Invoice #1, $300, Invoice #2, $200, etc until all invoices are paid or there is no money left from the payment. If there is an overpayment, then I want to somehow have that money become "unallocated" and have it for new invoices that will come.
  10. That's just it. There are no records in the join table. I don't know if I have to create them manually each time a new invoice is made or a payment is recieved. Or if there is a way to have this automated. I am not sure I want a valuelist showing up in the portal to choose from. I just want the portal to display all records from a particular account that have a balance. Maybe I have to have the valuelist in-order for the portal to display this?
  11. I am trying to create a payment solution for my existing database. I have everything else working right now, but no way to keep track of payments. As it appears from my searching on Google, I need to create a join table as I have a many to many relationship. So this is what I have now. Invoices:ID_Invoice ---<Invoice_Payments:id_invoice Invoice_Payments:id_payment>---Payments:ID_Payment Inside my join table, I have a field for Payment_Portion. This way I can designate how much of the payment will go to each invoice. Sometimes I get a payment for one invoice. Sometimes its for a couple. Sometimes its like a down-payment or something on that order. On my Payments layout, I have a field to look up the Account and pull that info from the Accounts table. That works good. I created a portal to the join table (Invoice_Payments) and added fields from the Invoices table to the portal. My problem is that I can not get anything to show up there. What I am trying to accomplish is that ANY invoice for the account above that has a balance more than zero will show up. This way I can select which invoice to apply the payment to and how much gets applied to each invoice. I have seen this done in the Core3CRM database, but since I am not looking to purchase new software at this point, I figured I could create the same thing in my database. But it has proven a little challenging right now. I am sure this is something very simple to do, but it is above my knowledge level. Any help or guidance will be greatly appreciated. Let me know if more information is required to help.
  12. I am working on making a database to keep track of time spent working on a project. I then wanted to add a field to make notes in of different things that I did. My problem is this. On the layout where I enter in my start and stop times, I want to be able to click a button and have a new window open up with a layout has a spot in there so I can enter in the tasks I did for that job. I have tried many different things and I can't seem to get it to work correctly. I started using the Time Cards template that came with FileMaker as a starting ground. I made my new layout and I have that working. Now I am trying to get my script to select the current date that I am entering stuff into, and open up the new window with that record. But I can't seem to get it to work for me. This is what I have so far. I think I am close, but then again, I might not be. My Script: Set Variable[$$Day1; Value:Time Billing::Day1] Allow User Abort [Off] Freeze Window Go to Related Record [From table" "Time Billing"; Using layout: "Pop up Sizing_Time - Tasks" (Time Billing); New Window] Show/Hide Status Area [Lock; Hide] Adjust Window [Resize to Fit] Go to Layout["Time - Tasks Detail - Day1" (Time Billing)] Go to Record/Request/Page [$$Day1] Pause/Resume Script [indefinitely] Close Window [Name:"Tasks Performed"; Current file] I am not sure if there is a better way to accomplish this as I have to have a layout for each day (7 Layouts). That means I would need more scripts. I would greatly appreciate any assistance or suggestions on how to do this.
  13. I think you are right. I need to some how tell it to grab date and get the year from the last invoice in my db. What is the best way to accomplish this? You also mentioned that I need to do the test before I create a new record. I have it set up right now to run the script when I switch to that layout. Is this the best way to do this or should I do it another way?
  14. I tried this and it works better than what I was using as now it doesn't reset every time I open up the invoices layout, but it also doesn't reset when I change the computer date to Jan 1 2010. I dont understand why I would use an "=" sign instead of the ">" sign. Shouldn't it be saying that if the current year is greater than year of Invoice date, reset?
  15. I am trying to figure out how to make my script check the year and when the year changes, (ex 2010), it resets the auto incremental number. I tried the following: If [Year (Get (CurrentDate)) > Year ( Invoices::Date_Created )] Set Next Serial Value [invoices:Invoice_ID_Counter; 1001] End If But it resets everytime I open up my invoices layout. I want it to only reset when the year changes from 2009 to 2010, 2010 to 2011, etc. Any help would be greatly appreciated.
  • Create New...

Important Information

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