InfoPath Dev

Hi experts,

I am very new to InfoPath and need some help.

I have set up a form in InfoPath that is connected to an Access db for receving and submitting. The submitting and receiving data is working fine.
The form tracks patient demographics, weight and measurements - i.e. when a new patient comes in their initial weight and measurements are entered via the form and submitted to the "initial" table of the db - then each time they visit, another view of the form is used to insert their weight and masurements into the "appointment" table of the db.

Here are my issues:

1. Calculations using fields from the two most recent records in the "appointment" table (e.g. todays appointment and last weeks appointment).

- currently I am finding it easy to use an expression box and calculate the difference between "initial" and "appointment" fields for the current appointment (i.e. the most recent appointment).

But I also need to be able to calculate the difference between two records (based on date) from the same table. Any ideas? Any help would be greatly appreciated.

2. Adding new "appointment" information via a form that does not show all the other "appointment" records. Obviously the "appointment" table is linked via a relationship with the "patient" table.

Ideally what we want is to be able to search the db for a patient (which currently works fine) and then change views (or even auto open a new form if necessary) to a form that we can input a single record into. At the moment when I try that if the patient record has multiple "appointment" records associated with it then they all show up on the new view. Again, any help would be appreciated.

Thanks

CaptainAi

1. If your data is structured in such a way, you might not need to use the date. For example, if you always add new records at the end of the table, you would assume that the last row is your current appointment, that the second to last row (i.e. row[position() = count(row)-1] ) is the last appointment, and the third to last row (i.e. row[position() = count(row)-2] ) is your appointment before that.

I don't know if that information is helpful to your situation, but I thought I'd mention it anyway.

2. I would avoid opening a new form unless absolutely necessary. It only invites unnecessary complications. To limit the appointments that show in the new view, use a filter on the repeating table (something like "position() = last()" assuming you always add new records at the end of the table).

Hi Greg,

Thank you for your assistance with the issues I had. I ahve changed my way of thinking regarding filtering and displaying data according to your advice and the filtered tables and sections now work great! Thanks.

As a kind of related issue - I am now trying to display calculations using expression boxes in a standard layout table. i.e. each expression box either calls and displays the contents of a field or has its own function based on calling and processing different fields (e.g. sum, multiply, divide, complex maths). Most of these are working absolutely fine. However, when I come to trying to base an expression box on a field or calculation using the last appointment details (i.e. using filter position() = last() - 1) - it doesn't work - meaning it won't call the previous appointment details. I think this may be to do with the fact that the expression box is not within the section I want and I am trying to filter a control via conditional formatting as filtering doesn't appear to be an option with expression boxes also the control is not related to the section....? I am probably missing some logic here or something. If I sound confused - it because I am... lol. Any help would be much appreciated.

I have another issue - I will also post this as a seperate question since it is unrelated to the calculation issue. Here goes:

I am using and expression box with a concat() function that calls 5 fileds with the last one being a date field (date of birth) - this is for the subject line of a letter that is auto-generated in a view of the form. I have spaced and added static content within the function. All of the data displays 100% correct- even the date, except that the date format diplays like this: "1973-11-23T00:00:00".

The field it calls (date of birth) is a properly formatted field (dd/mm/yyyy)with "do not display time" selected. The fields' value is stored in the database as short time date field, again with no time attribute. Why would the format become yyyy-mm-dd and have a time format added on to it just by calling the field in an expression box?

Obviously I could just call the date of birth seperately in a date picker (and format it - next to the other data in the expression box) instead of adding it into the concat() expression in the expression box but I find that aesthetically, using an expression box with concat()is much easier as it auto-arranges the called data (i.e. you don't get big gaps or partial data when data lengths change from record to record). So if there is anyway of formatting the called date within the concat() expression in the expression box I would love to know. Thanks.