If statement for assigning the serial#

[MikeAir]

Hi everybody,

 

I am new to both filemaker and this forum, so i hope i have posted this topic in the right place.

 

I am building my first ERP system, and things are going well, but now i have reached a point where i need some help.

 

The task for my system:

I have 2 'independent' tables: Orders & BankTransfers. And i would like to assign the bank transfers which have the same payment amount as the orders, so that i can see the differences between the bank account transfers and the data in my ERP system.

 

 

So I would like to assign / connect orders which have certain criterias, with bank transfers that matches these criterias. Theese criterias would be the 'date' field and the 'amount' field, so that if these two fields match, in each of the two tables, a 3rd field(PaymentControl) in BankTransfers will be positive(1), and if not, negative(0).

 

So i figured to create this 3rd field as a calculation with an if-statement:

If ( Amount = BankTransfers::Amount; "1" ; "0" )

I would guess that if i want it to check if the date also matches, i just need to put an '& BankTransfers::Date = Orders::Date' after the amount-test. Is that correct?

 

But this calculation does not work - what am i doing wrong?? 

 

I've uploaded some  screenshots:

 

Orders table

 

BankTransfers table

 

Relationship settings

 

Orders field settings

 

BankTransfers field settings

 

 

Thank you,

Mike

[eos]

You're trying to read values via a relationship that you want to set, based on these criteria, in the first place – that just cannot work.

 

I assume that, in theory, an order can be paid in installments, so one order could have several transfers, but each transfer would belong to one order. In that case, you need a foreign orderID in the BankTransfer table, instead of the other way around. (Could there ever be a many-to-many structure, where a payment could be distributed to several orders …?)

 

Now set up a relationship defined as

 

Orders::theDate = BankTransfers::theDate

Orders::amount = BankTransfers::amount

 

to display (within a portal) a list of “candidates” – transfer records matching date and amount. No utility fields necessary.

 

Add a portal filter to show only transfers that have not yet been assigned to an order, using the predicate

IsEmpty ( BankTransfers::FK_OrderID )

Use that portal and a script to assign a candidate as an actual payment – i.e. set its orderFK to the current orderID.

 

Use a normal relationship like

 

Orders::orderID = BankTransfers::FK_OrderID

 

to display all transfers assigned to the current order.

 

You can use a calculation field like Sum ( BankTransfers::amount ) to calculate the total of all transfers, and compare that to the order amount to get/set a payment status.

 

Note that it's better to use normal (non-calculated) fields that you set to values calculated just-in-time, as triggered by certain events; but as a first try / POC and with just a few records, calc fields should be OK.

[MikeAir]

You're trying to read values via a relationship that you want to set, based on these criteria, in the first place – that just cannot work.

 

I assume that, in theory, an order can be paid in installments, so one order could have several transfers, but each transfer would belong to one order. In that case, you need a foreign orderID in the BankTransfer table, instead of the other way around. (Could there ever be a many-to-many structure, where a payment could be distributed to several orders …?)

 

Now set up a relationship defined as

 

Orders::theDate = BankTransfers::theDate

Orders::amount = BankTransfers::amount

 

to display (within a portal) a list of “candidates” – transfer records matching date and amount. No utility fields necessary.

 

Add a portal filter to show only transfers that have not yet been assigned to an order, using the predicate

IsEmpty ( BankTransfers::FK_OrderID )

Use that portal and a script to assign a candidate as an actual payment – i.e. set its orderFK to the current orderID.

 

Use a normal relationship like

 

Orders::orderID = BankTransfers::FK_OrderID

 

to display all transfers assigned to the current order.

 

You can use a calculation field like Sum ( BankTransfers::amount ) to calculate the total of all transfers, and compare that to the order amount to get/set a payment status.

 

Note that it's better to use normal (non-calculated) fields that you set to values calculated just-in-time, as triggered by certain events; but as a first try / POC and with just a few records, calc fields should be OK.

 

Thank you for your reply.

 

To your first question: there could potentially be situations where the costumer pays in rates, and pays for more than one order - but lets keep it simple, cause i feel like i need to get the basics right at first.

 

And thank you for your inspiration on how to make the verification/assignment of transactions to each order. I built a list from two portals, one showing the filtered matching transactions without foreign keys attached(those that have not been assigned yet), and another right below showing all of the matching transactions(both assigned, and not assigned).

 

I think that a script is too automated for this task, cause i need to be sure and manually approve each assignment of keys.

 

And i also thought about another form showing me all of the transactions, and all of the orders in a list / portal in the same layout? Is that possible at all?

This might help me assigning transactions that is not matching the price or the date 100%. 

 

I have been trying all day, but i cannot figure out how to make the summary field so that it calculates all the transactions that have been assigned with a foreign key. Can you be a little more specific(im still new to filemaker)?

 

You can download my file here, if you want to see how far i made it 

https://dl.dropboxusercontent.com/u/3592722/FMTest.fmp12 

[Lee Smith]

 

You can download my file here, if you want to see how far i made it 

 

No. you need to post your file here. Follow the steps here. ATTACH FILE  

Let me know if you have any problems.

 

Files need to be posted here for a number of reasons, mainly because they have a tendency to disappear once the OP has gotten a solution to their problem.

 

If you need to have more information about this, contact me via Private Message.

 

Lee

[MikeAir]

No. you need to post your file here. Follow the steps here. ATTACH FILE  

Let me know if you have any problems.

 

Files need to be posted here for a number of reasons, mainly because they have a tendency to disappear once the OP has gotten a solution to their problem.

 

If you need to have more information about this, contact me via Private Message.

 

Lee

 

Im sorry. I tried to attach the file when i created the topic, but it wouldnt allow me to upload the .fmp12 fileformat.

But now i can see that it only accepts Zip format.

 

Here is the file:

FMTest.fmp12.zip

[eos]

Hi Mike,

 

please don't overquote in your reply posts. If someone wants to read my post in its entirety, its still there. Quote to highlight any sections you are responding to in particular – e.g. … 

 

I think that a script is too automated for this task, cause i need to be sure and manually approve each assignment of keys.

 

That would be a script that you trigger, after you inspected the candidates. Your launching the script then is that act of approval.

 

Anyway, here's a sample file that should get you going. Note how assigning / removing a payment updates the payment status, and that the sum display is a related summary field from the payments table. Also note the space-saving popover for the candidate display. The Popover button has a hide condition that triggers if there aren't any candidates.

 

One possible improvement for the popover: if there aren't any date/amount candidates for panel #1, immediately go to panel #2 (all unassigned). See if you can try and figure out how to recognise that condition and act upon it. 

 

Before you continue building a user interface, think about the possible scenarios. Do you want to …

 

• assign one or more payments to an order, from the context of an Orders record?

• assign one order to a payment, from the context of a Payments record?

• do both or either from a neutral context?

 

The sample file shows how to do it from the Orders context; you can build something analogous for the other scenarios.

 

If you want to assign (split, actually) multiple payments to/between multiple orders, you need a join table with an allocation amount field. Maybe let's do this another time … 

FMTest_eosMOD.fmp12.zip

[MikeAir]

 

please don't overquote in your reply posts. If someone wants to read my post in its entirety, its still there. Quote to highlight any sections you are responding to in particular – e.g. … 

 

I'm sorry! I am new to this forum, so mistakes happen.

 

 

Anyway, here's a sample file that should get you going. Note how assigning / removing a payment updates the payment status, and that the sum display is a related summary field from the payments table. Also note the space-saving popover for the candidate display. The Popover button has a hide condition that triggers if there aren't any candidates.

 

 

Thank so much! This is very great. It is very close to what i had in mind, even though i didnt know about the popover button feature. Very smart.

I think i am going to analyze everything you have done the next couple of days, to try to understand FM better.

One little thing though - the subtotal isn't calculating the assigned orders? I think that is the only last thing i would need before i can use it in practice.

 

How can i do that? So that it would summarize every transaction that has been assigned to the current order?

 

 

Before you continue building a user interface, think about the possible scenarios. Do you want to …

 

• assign one or more payments to an order, from the context of an Orders record?

• assign one order to a payment, from the context of a Payments record?

• do both or either from a neutral context?

 

The sample file shows how to do it from the Orders context; you can build something analogous for the other scenarios.

 

If you want to assign (split, actually) multiple payments to/between multiple orders, you need a join table with an allocation amount field. Maybe let's do this another time … 

 

 

Well, i am the accountant that will be viewing/using the data in the end of the year, so in the rare occurrences of 1 payment with 2 different orders i will change the data manually in the table view. The company is a management & booking firm that is selling concerts, and it rarely occur that a costumer pays for 2 concerts, because the customer often is the venue itself.

 

If it were so, that the costumer placed an order of 3 different concerts, they could just do that in one order right?

- do you think my current design will support having more items(koncert a, koncert b, koncert c) in one order?

I just need to add a table(OrderItems maybe?) specifying the order details, right?

 

Hope you know that i am very grateful for you help!

[eos]

One little thing though - the subtotal isn't calculating the assigned orders? I think that is the only last thing i would need before i can use it in practice.

How can i do that? So that it would summarize every transaction that has been assigned to the current order?

 

“Subtotal”? Do you mean the summary field? If so, then you should know that a summary field summarises a found or related set of records. The same summary field …

 

• in a sub-summary part summarises the records in that group (found set needs to be sorted by the part's break field to get correct results)

• in a grand summary part summarises the complete found set

• as a related field returns the results for the related records from the same TO

 

where the last point applies to the summary field on your Orders layout: it displays a total for the assigned payments (i.e. related via the orderID, not the payments displayed as candidates).

 

If it were so, that the costumer placed an order of 3 different concerts, they could just do that in one order right?

- do you think my current design will support having more items(koncert a, koncert b, koncert c) in one order?

I just need to add a table(OrderItems maybe?) specifying the order details, right?

 

Look at a common invoicing solution:

 

Customers --< Orders --< LineItems >-- Items

 

You need to know what your items are.

 

Concerts, or tickets? ( e.g.  … --< LineItems >-- Tickets >-- Concerts)

 

In the end, “Items” are just the chargeable services and/or products that you offer to your prospective customers.

 

PS: There's a bug in the “unassign” section of the ManagePayments script in my sample file …

[MikeAir]

Subtotal is a Danish term, im sorry: but it is the same as a subsummary.

 

I've added Products and items for each order. And actually your script & popover button works like a charm.

 

It is actually working the way i figured in my head, so i am very pleased right now. Now i just need to add Costumers, and other stuff that. And then in some weeks i am going to add a dashboard layout with some graphs.

 

In DK it has recently become legal to have the reciepts/annexes in a digital format(picture) on the server, so i am also going to add a recepit/annex table to the system as a container field. Do you have any advice for me in regards to storing these images and not slowing the database?

 

I am not quite sure if it stores the image inside the database, or just stores the images in a folder and then links to them from the databse(webdirect)..

 

Have a look at my modification of your payment assigment system 

FMTEST_Vers2.fmp12.zip

go to order #7 - i have added some conditional formatting for helping me controlling if the value allocated is the same as the order. 

Payment list is also conditionally formatted for easily showing me the transactions that are not allocated.

 

I was doing this manually in excel a year ago 

[MikeAir]

Okay, now I am in trouble again..

 

I need to destinguish between expenses(annexes) and orders, and also need to relate each expence to an order.

At the same time, i need to use the payment-asignment system for helping me with the books..

 

I figured the easiest way would be to take your scripts, the layout and the table relations, and just copy-paste it, with another name for my annexes table.

And I got pretty far that way... Im just not there yet.. 

 

- Payments assigned to annexes does not vanish from the list, after assigning them to an Annex. For instance there is the possibility that i can both assign a transaction to an order and an Annex.

- Payment status doesent update after assigning the right amount in the annex payment assignment form

 

However, the order payment assignment table still works perfect.

 

Maybe you can tell me where it went wrong, or which script trigger i did miss?

 

Replikation.fmp12.zip

[MikeAir]

Okay - now i have replicated all the pieces from the script/database structure Eos made for me. And it is possible to assign the transactions both to orders and to annexes. Very good!

 

But if a banktransfer has been assigned to an order, it will no longer be available for the other orders, which is good - but it will still be available for all of the annexes. 

 

Any ideas how to solve this problem? I thought about letting each of the assignment forms assign a "dummy" number to the opposite field.

 

Example:

 

I assign Tansaction #1 for order #2, and by doing this the form also assigns a "1" in the FK_Annexes field, with the result that when i go to the Annex-asignment form the transaction #1 will have the state as assigned to annex #1.

 

Then i would just have to leave order #1 and annex #1 to be blank, so that the dummy number will not make an assignment to an existing transaction.

 

What do you think? Should i change this in the script or the form?

 

 

Replikation.fmp12.zip